Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 3D8091A6A985 for ; Sat, 21 Jul 2012 13:58:48 -0300 (ADT) Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Ssd0s-0006vY-Rb for pgsql-performance@postgresql.org; Sat, 21 Jul 2012 16:58:47 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id q6LGwK4c020580; Sat, 21 Jul 2012 12:58:20 -0400 (EDT) From: Tom Lane To: Ioannis Anagnostopoulos cc: Marc Mamin , Claudio Freire , pgsql-performance@postgresql.org Subject: Re: A very long running query.... In-reply-to: <500A74C1.8020109@anatec.com> 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> Comments: In-reply-to Ioannis Anagnostopoulos message dated "Sat, 21 Jul 2012 10:22:09 +0100" Date: Sat, 21 Jul 2012 12:58:20 -0400 Message-ID: <20579.1342889900@sss.pgh.pa.us> X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201207/255 X-Sequence-Number: 47436 [ 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