From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Claudio Freire <klaussfreire(at)gmail(dot)com> |
Cc: | Ioannis Anagnostopoulos <ioannis(at)anatec(dot)com>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: A very long running query.... |
Date: | 2012-07-20 23:10:02 |
Message-ID: | 3639.1342825802@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice pgsql-performance |
Claudio Freire <klaussfreire(at)gmail(dot)com> writes:
> Looking at this:
> " -> Index Scan using
> idx_message_copies_wk2_date_src_pos_partial on message_copies_wk2
> message_copies (cost=0.00..19057.93 rows=52 width=32) (actual
> time=62.124..5486270.845 rows=387524 loops=1)"
> " Index Cond: ((date_trunc('day'::text,
> msg_date_rec) = '2012-07-17 00:00:00'::timestamp without time zone)
> AND (src_id = 1))"
> " Filter: ((date_part('day'::text,
> msg_date_rec) = 17::double precision) AND (NOT (((((pos_georef1)::text
> || (pos_georef2)::text) || (pos_georef3)::text) ||
> (pos_georef4)::text) IS NULL)) AND (((((pos_georef1)::text ||
> (pos_georef2)::text) || (pos_georef3)::text) || (pos_georef4)::text)
> <> ''::text))"
I think the real problem is that the planner has no hope of doing
anything very accurate with such an unwieldy filter condition. I'd look
at ways of making the filter conditions simpler, perhaps by recasting
the data representation. In particular, that's a horridly bad way of
asking whether some columns are empty, which I gather is the intent.
If you really want to do it just like that, creating an index on the
concatenation expression would guide ANALYZE to collect some stats about
it, but it would probably be a lot more efficient to put together an AND
or OR of tests on the individual columns.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Prima Chairunnanda | 2012-07-20 23:27:44 | Backend freezes at "idle in transaction" while using libpq PQexec (PostgreSQL version 9.1.2) |
Previous Message | Ioannis Anagnostopoulos | 2012-07-20 22:19:15 | Re: A very long running query.... |
From | Date | Subject | |
---|---|---|---|
Next Message | Ioannis Anagnostopoulos | 2012-07-20 23:56:34 | Re: A very long running query.... |
Previous Message | Ioannis Anagnostopoulos | 2012-07-20 22:19:15 | Re: A very long running query.... |