Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 040991A6A985 for ; Sat, 21 Jul 2012 14:42:51 -0300 (ADT) Received: from mail.anatec.com ([87.84.168.242]) by magus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1SsdhV-0007by-Sm for pgsql-performance@postgresql.org; Sat, 21 Jul 2012 17:42:50 +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 18:42:32 +0100 Message-ID: <500AEA07.1070602@anatec.com> Date: Sat, 21 Jul 2012 18:42:31 +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: Tom Lane CC: Marc Mamin , 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> <500A74C1.8020109@anatec.com> <20579.1342889900@sss.pgh.pa.us> In-Reply-To: <20579.1342889900@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------080604030501050305020406" X-OriginalArrivalTime: 21 Jul 2012 17:42:32.0372 (UTC) FILETIME=[34CCF740:01CD6768] X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201207/256 X-Sequence-Number: 47437 This is a multi-part message in MIME format. --------------080604030501050305020406 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 21/07/2012 17:58, Tom Lane wrote: > [ Please try to trim quotes when replying. People don't want to re-read > the entire thread in every message. ] > > Ioannis Anagnostopoulos writes: >> On 21/07/2012 10:16, Marc Mamin wrote: >>> 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' >> No because it is used to select a partition. Otherwise it will go >> through the whole hierarchy... > You're using extract(day...) to define partitions? You might want to > rethink that. The planner has got absolutely no intelligence about > the behavior of extract, and in particular doesn't realize that the > date_trunc condition implies the extract condition; so that's another > part of the cause of the estimation error here. > > What's usually recommended for partitioning is simple equality or > range constraints, such as "msg_date_rec >= 'date1' AND > msg_date_rec < 'date2'", which the planner does have a fair amount > of intelligence about. > > Now, you can generalize that to equality or range constraints using > an expression; for instance there'd be no problem to partition on > date_trunc('day', msg_date_rec) rather than msg_date_rec directly, > so long as your queries always use that same expression. But you > should not expect that the planner can deduce very much about the > correlations between results of different functions. > > regards, tom lane I think you got this wrong here. If you see the query again you will see that I do use equality. The problem is that my "equality" occurs by extracting the date from the msg_date_rec column. To put it in other words, for not using the "extract" I should have an additional column only with the "date" number to perform the equality. Don't you feel that this is not right since I have the actual date? The constrain within the table that defines the partition is as follows: CONSTRAINT message_copies_wk0_date CHECK (date_part('day'::text, msg_date_rec) >= 1::double precision AND date_part('day'::text, msg_date_rec) <= 7::double precision) I see not problem at this. The planner gets it right and "hits" the correct table every time. So unless if there is a technique here that I completely miss, where is the problem? Regards Yiannis --------------080604030501050305020406 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
On 21/07/2012 17:58, Tom Lane wrote:
[ Please try to trim quotes when replying.  People don't want to re-read
  the entire thread in every message. ]

Ioannis Anagnostopoulos <ioannis@anatec.com> writes:

On 21/07/2012 10:16, Marc Mamin wrote:

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'


      

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


      

      
You're using extract(day...) to define partitions?  You might want to
rethink that.  The planner has got absolutely no intelligence about
the behavior of extract, and in particular doesn't realize that the
date_trunc condition implies the extract condition; so that's another
part of the cause of the estimation error here.

What's usually recommended for partitioning is simple equality or
range constraints, such as "msg_date_rec >= 'date1' AND
msg_date_rec < 'date2'", which the planner does have a fair amount
of intelligence about.

Now, you can generalize that to equality or range constraints using
an expression; for instance there'd be no problem to partition on
date_trunc('day', msg_date_rec) rather than msg_date_rec directly,
so long as your queries always use that same expression.  But you
should not expect that the planner can deduce very much about the
correlations between results of different functions.

			regards, tom lane


    

    I think you got this wrong here. If you see the query again you will
    see that I do use equality. The problem is that my "equality" occurs

    by extracting the date from the msg_date_rec column. To put it in
    other words, for not using the "extract" I should have an additional

    column only with the "date" number to perform the equality. Don't
    you feel that this is not right since I have the actual date? The
    constrain

    within the table  that defines the partition is as follows:

    

    CONSTRAINT message_copies_wk0_date CHECK
        (date_part('day'::text, msg_date_rec) >= 1::double precision
        AND date_part('day'::text, msg_date_rec) <= 7::double
        precision)

    

    I see not problem at this. The planner gets it right and "hits" the
    correct table every time. So unless if there is a technique here
    that I completely miss, 

    where is the problem?

    

    

    Regards

    Yiannis

  


--------------080604030501050305020406--