From: Tom Lane <tgl@sss.pgh.pa.us>
To: Claudio Freire <klaussfreire@gmail.com>
Cc: Ioannis Anagnostopoulos <ioannis@anatec.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: A very long running query....
Date: Fri, 20 Jul 2012 19:10:02 -0400
Message-ID: <3639.1342825802@sss.pgh.pa.us> (raw)
In-Reply-To: <CAGTBQpYY1cVYZtVNt9mdHjhZy=wQZac-aERAcmkeXs2D+Ym7cA@mail.gmail.com>
References: <5007FC82.9070007@anatec.com>
<5009CB5A.7070801@anatec.com>
<CAGTBQpa07PMrLA3qsM9Zf9QXcPvgntyVX7qL9CqEwQtK9LH1hA@mail.gmail.com>
<5009CD43.8000300@anatec.com>
<CAGTBQpYY1cVYZtVNt9mdHjhZy=wQZac-aERAcmkeXs2D+Ym7cA@mail.gmail.com>
Claudio Freire <klaussfreire@gmail.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, klaussfreire@gmail.com, ioannis@anatec.com
Subject: Re: A very long running query....
In-Reply-To: <3639.1342825802@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox