From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ioannis Anagnostopoulos <ioannis@anatec.com>
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 12:58:20 -0400
Message-ID: <20579.1342889900@sss.pgh.pa.us> (raw)
In-Reply-To: <500A74C1.8020109@anatec.com>
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>
[ 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
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: tgl@sss.pgh.pa.us, ioannis@anatec.com, M.Mamin@intershop.de, klaussfreire@gmail.com
Subject: Re: A very long running query....
In-Reply-To: <20579.1342889900@sss.pgh.pa.us>
* 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