Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 7BF741A6A984 for ; Sat, 21 Jul 2012 06:22:27 -0300 (ADT) Received: from mail.anatec.com ([87.84.168.242]) by magus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1SsVtG-0000DR-LP for pgsql-performance@postgresql.org; Sat, 21 Jul 2012 09:22:26 +0000 Received: from [192.168.10.57] ([192.168.10.57]) by mail.anatec.com over TLS secured channel with Microsoft SMTPSVC(6.0.3790.4675); Sat, 21 Jul 2012 10:22:09 +0100 Message-ID: <500A74C1.8020109@anatec.com> Date: Sat, 21 Jul 2012 10:22:09 +0100 From: Ioannis Anagnostopoulos User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:14.0) Gecko/20120713 Thunderbird/14.0 MIME-Version: 1.0 To: Marc Mamin CC: Claudio Freire , pgsql-performance@postgresql.org Subject: Re: A very long running query.... References: <5007FC82.9070007@anatec.com> <5009CB5A.7070801@anatec.com> <5009CD43.8000300@anatec.com> <3639.1342825802@sss.pgh.pa.us> <5009F032.40002@anatec.com> In-Reply-To: Content-Type: multipart/alternative; boundary="------------090105060302060005020608" X-OriginalArrivalTime: 21 Jul 2012 09:22:09.0604 (UTC) FILETIME=[4DD62040:01CD6722] X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201207/254 X-Sequence-Number: 47435 This is a multi-part message in MIME format. --------------090105060302060005020608 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 21/07/2012 10:16, Marc Mamin wrote: > RE: [PERFORM] A very long running query.... > > Hello, > isn't the first test superfluous here ? > > > where extract('day' from message_copies.msg_date_rec) = 17 > > and date_trunc('day', message_copies.msg_date_rec) = '2012-07-17' > > > > Here is the index: > > > > CREATE INDEX idx_message_copies_wk2_date_src_pos_partial > > ON feed_all_y2012m07.message_copies_wk2 > > USING btree > > (date_trunc('day'::text, msg_date_rec), > > src_id, > > (((pos_georef1::text || pos_georef2::text) || pos_georef3::text) > || pos_georef4::text)) > > TABLESPACE archive > > WHERE (((pos_georef1::text || pos_georef2::text) || > pos_georef3::text) || pos_georef4::text) IS NOT NULL > > OR NOT (((pos_georef1::text || pos_georef2::text) || > pos_georef3::text) || pos_georef4::text) = ''::text; > > > the georef test can be simplified using coalesce: > > > and (message_copies.pos_georef1 || message_copies.pos_georef2 > || message_copies.pos_georef3 || message_copies.pos_georef4) <> '' > > and not (message_copies.pos_georef1 || message_copies.pos_georef2 > || message_copies.pos_georef3 || message_copies.pos_georef4) is null > => > and coaesce ( > (message_copies.pos_georef1 || message_copies.pos_georef2 || > message_copies.pos_georef3 || message_copies.pos_georef4), > '') <> '' > > In order to avoid this test at query time you might add a boolean > column message_copies.pos.has_georef, > and keep it up to date with a before insert or update trigger. This > will allow to shorten your index definition and simplify the planner > task a little bit. > Moreover it will fasten your query in cases when the index don't get used. > > As Tom already mentioned it, it may make sense not to concatenate the > georef within the index, but keep them separated, or even keep them in > different indexes. > Which is the best depend on the other queries running against this table > > HTH, > > Marc Mamin > > > > -----Original Message----- > From: pgsql-performance-owner@postgresql.org on behalf of Ioannis > Anagnostopoulos > Sent: Sat 7/21/2012 1:56 AM > To: Tom Lane > Cc: Claudio Freire; pgsql-performance@postgresql.org > Subject: Re: [PERFORM] A very long running query.... > > On 21/07/2012 00:10, Tom Lane wrote: > > Claudio Freire 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 > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > > No because it is used to select a partition. Otherwise it will go through the whole hierarchy... --------------090105060302060005020608 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
On 21/07/2012 10:16, Marc Mamin wrote:
RE: [PERFORM] A very long running query....

Hello,
isn't the first test superfluous here ?

>       where extract('day' from message_copies.msg_date_rec) = 17
>       and date_trunc('day',    message_copies.msg_date_rec) = '2012-07-17'


> Here is the index:
>
> CREATE INDEX idx_message_copies_wk2_date_src_pos_partial
>   ON feed_all_y2012m07.message_copies_wk2
>   USING btree
>   (date_trunc('day'::text, msg_date_rec),
>   src_id,
>   (((pos_georef1::text || pos_georef2::text) || pos_georef3::text) || pos_georef4::text))
> TABLESPACE archive
>   WHERE (((pos_georef1::text || pos_georef2::text) || pos_georef3::text) || pos_georef4::text) IS NOT NULL
>   OR NOT (((pos_georef1::text || pos_georef2::text) || pos_georef3::text) || pos_georef4::text) = ''::text;


the georef test can be simplified using coalesce:

>  and     (message_copies.pos_georef1 || message_copies.pos_georef2 || message_copies.pos_georef3 || message_copies.pos_georef4) <> ''
>  and not (message_copies.pos_georef1 || message_copies.pos_georef2 || message_copies.pos_georef3 || message_copies.pos_georef4) is null
  =>
  and coaesce (
    (message_copies.pos_georef1 || message_copies.pos_georef2 || message_copies.pos_georef3 || message_copies.pos_georef4),
     '') <> ''
 
In order to avoid this test at query time you might add a boolean column   message_copies.pos.has_georef,
and keep it up to date  with a before insert or update trigger. This will allow to shorten your index definition and simplify the planner task a little bit.
Moreover it will fasten your query in cases when the index don't get used.

As Tom already mentioned it, it may make sense not to concatenate the georef within the index, but keep them separated, or even keep them in different indexes.
Which is the best depend on the other queries running against this table
 
HTH,

Marc Mamin
 


-----Original Message-----
From: pgsql-performance-owner@postgresql.org on behalf of Ioannis Anagnostopoulos
Sent: Sat 7/21/2012 1:56 AM
To: Tom Lane
Cc: Claudio Freire; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] A very long running query....

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

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

No because it is used to select a partition. Otherwise it will go through the whole hierarchy...
--------------090105060302060005020608--