From: Ioannis Anagnostopoulos <ioannis@anatec.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Marc Mamin <M.Mamin@intershop.de>
Cc: Claudio Freire <klaussfreire@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: A very long running query....
Date: Sat, 21 Jul 2012 18:42:31 +0100
Message-ID: <500AEA07.1070602@anatec.com> (raw)
In-Reply-To: <20579.1342889900@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>
<5009F032.40002@anatec.com>
<C4DAC901169B624F933534A26ED7DF310F96B02D@JENMAIL01.ad.intershop.net>
<500A74C1.8020109@anatec.com>
<20579.1342889900@sss.pgh.pa.us>
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
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, M.Mamin@intershop.de, klaussfreire@gmail.com
Subject: Re: A very long running query....
In-Reply-To: <500AEA07.1070602@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