From: Ioannis Anagnostopoulos <ioannis@anatec.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Claudio Freire <klaussfreire@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: A very long running query....
Date: Sat, 21 Jul 2012 00:56:34 +0100
Message-ID: <5009F032.40002@anatec.com> (raw)
In-Reply-To: <3639.1342825802@sss.pgh.pa.us>
References: <5007FC82.9070007@anatec.com>
<5009CB5A.7070801@anatec.com>
<CAGTBQpa07PMrLA3qsM9Zf9QXcPvgntyVX7qL9CqEwQtK9LH1hA@mail.gmail.com>
<5009CD43.8000300@anatec.com>
<CAGTBQpYY1cVYZtVNt9mdHjhZy=wQZac-aERAcmkeXs2D+Ym7cA@mail.gmail.com>
<3639.1342825802@sss.pgh.pa.us>
On 21/07/2012 00:10, Tom Lane wrote:
> 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
So what you suggest is to forget all together the concatenation of the
georef1/2/3/4 and instead alter my query with something like:
georef1 is not null and not georeg1 = ''....etc for georef2 3 and 4
That would require to alter my index and have the four georef columns
separately in it and not as a concatenation and so on for the partial
index part. And a final thing, you seem to imply that the indexes are
used by the analyser to collect statistics even if they are not used. So
an index serves not only as a way to speed up targeted queries but also
to provide better statistics to the analyzer?
Kind Regards
Yiannis
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: ioannis@anatec.com, tgl@sss.pgh.pa.us, klaussfreire@gmail.com
Subject: Re: A very long running query....
In-Reply-To: <5009F032.40002@anatec.com>
* 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