Re: A very long running query....

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