Skip site navigation (1) Skip section navigation (2)

Re: A very long running query....

From: Ioannis Anagnostopoulos <ioannis(at)anatec(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Marc Mamin <M(dot)Mamin(at)intershop(dot)de>, Claudio Freire <klaussfreire(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: A very long running query....
Date: 2012-07-21 17:42:31
Message-ID: 500AEA07.1070602@anatec.com (view raw or flat)
Thread:
Lists: pgsql-novicepgsql-performance
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(at)anatec(dot)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

In response to

pgsql-novice by date

Next:From: Ioannis AnagnostopoulosDate: 2012-07-21 19:16:36
Subject: Re: A very long running query....
Previous:From: Tom LaneDate: 2012-07-21 16:58:20
Subject: Re: A very long running query....

pgsql-performance by date

Next:From: Ioannis AnagnostopoulosDate: 2012-07-21 19:16:36
Subject: Re: A very long running query....
Previous:From: Tom LaneDate: 2012-07-21 16:58:20
Subject: Re: A very long running query....

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group