Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id BD4331337B82 for ; Thu, 21 Apr 2011 22:26:37 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 71218-05 for ; Fri, 22 Apr 2011 01:26:20 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from nm13.bullet.mail.ne1.yahoo.com (nm13.bullet.mail.ne1.yahoo.com [98.138.90.76]) by mail.postgresql.org (Postfix) with SMTP id A1F80133655C for ; Thu, 21 Apr 2011 22:26:19 -0300 (ADT) Received: from [98.138.90.53] by nm13.bullet.mail.ne1.yahoo.com with NNFMP; 22 Apr 2011 01:26:19 -0000 Received: from [98.138.88.237] by tm6.bullet.mail.ne1.yahoo.com with NNFMP; 22 Apr 2011 01:26:19 -0000 Received: from [127.0.0.1] by omp1037.mail.ne1.yahoo.com with NNFMP; 22 Apr 2011 01:26:19 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 646330.82905.bm@omp1037.mail.ne1.yahoo.com Received: (qmail 93543 invoked by uid 60001); 22 Apr 2011 01:26:19 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1303435579; bh=64nLHT/BSGmya/6xr9gBnenVeo/V0aY9eXaTL0LYrDM=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type; b=NLlj1mcGGlFsGHJR3hK/lNNLr5C2C7gN8A7jiygK6tfzjbweZ9sMiU1nBzPzf2yGZXdpG4UjOHgCFSo5X6E7Q5JwTRdoO7N9X32HD1hV/VbABUKruJdANa9vaUAXKA95t4ihyinH2mCP4BumLQ1GJAzyyTEq7oQoV+cM8MmAh3Q= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type; b=yQPDrkiQ/bkQpMo4SQV1QAmRc1M0taQ4Rya8wCYxKNO0CvmSEFFm0k634FoGF+eHBi7MrD0igJMcA5xy+Qr9AwqKxAIekgVf55DNhVvH4Nqpc/6i+IzgzfZYeqTrjWTwT6gpPauwu1HY5TMp1xnkUyC4ywzCcMV3DGa1ZCQA91A=; Message-ID: <505432.93484.qm@web120209.mail.ne1.yahoo.com> X-YMail-OSG: fc1aStQVM1nM65XL8Quxj1K3j5_Y13Ov7cOyBFtPh0Dh1ll eutdkwlbqjreM9ltFKpL7RwBBrpiPLtmmgSvknbFhBpKUyhoHo9858zed9T. Xr.ZDzum88BY8DQxIoeHlHqounnC0mPzKxaRfqWxl9CKuhu.UxCEdnWRUSSW Ujg4eq21.AIvxedyqT1VeQ8aXct_L9fSt80.97T8tfkS9fBr2IWSzzdMnc_0 HNT07jD3TVWdIJAZnVPZY8L_X6ZGYFWtOlSPk00BuFxT2MVXFk6FycXXGwmv tEku4DF6FtjDT.HaCb91nz92hT4rbvbJ.EGN8ERnk0Q63EtvvPlPI Received: from [207.171.18.94] by web120209.mail.ne1.yahoo.com via HTTP; Thu, 21 Apr 2011 18:26:18 PDT X-Mailer: YahooMailRC/559 YahooMailWebService/0.8.109.295617 Date: Thu, 21 Apr 2011 18:26:18 -0700 (PDT) From: Paul Pierce Subject: Issue with partition elimination To: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-16951286-1303435578=:93484" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.344 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_ENVFROM_END_DIGIT=1.553, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, RFC_ABUSE_POST=0.001, T_RP_MATCHES_RCVD=-0.01, T_TO_NO_BRKTS_FREEMAIL=0.01 X-Spam-Level: X-Archive-Number: 201104/336 X-Sequence-Number: 43398 --0-16951286-1303435578=:93484 Content-Type: text/plain; charset=us-ascii This is a good one :) Here is a "brief" description of our issue(Postgres 9.0): Tables: main fact table: Table "public.parent_fact" Column | Type | ----------------------+-----------------------------+----------- etime | date | not null pcamp_id | integer | location_id | integer | impressions | bigint | clicks | int this table partitioned by etime. We are trying to build a report, which has last week numbers alongside with this week numbers. For example: if today is Wednesday, I want to compare daily numbers from last week 3 days (mon through wed) with this week 3 days(mon through wed). To accomplish that, we've decided to build a transformation table, which has two columns: Table "public.trans_last_week" Column | Type | Modifiers ----------+-----------------------------+----------- etime | date | lw_etime | date | So for each date(etime), we have lw_etime, which is essentially etime-7 days. Here is the first query, which performs fine: select a11.location_id AS location_id, a11.pcamp_id AS pcamp_id, sum(a11.clicks) from parent_fact a11 where a11.etime between '2011-14-18' and '2011-04-20' group by a11.location_id, a11.pcamp_id everything is good there - it calculates numbers from the current week and goes to only 3 partitions to aggregate numbers. Here is the second query: select a11.location_id AS location_id, a11.pcamp_id AS pcamp_id, sum(a11.clicks) from parent_fact a11 join trans_last_week a12 on (a11.etime = a12.lw_etime) where a12.etime between '2011-14-18' and '2011-04-20' group by a11.location_id, a11.pcamp_id Here it scans through all partitions in the parent_fact table and runs 3-4 times slower. What was noticed, that the only case when Postgres is actually going to execute the query against the right partitions is query #1. Is that by design? Second query join, will also result in 3 days(3 partitions) This query (#3) also scans all partitions: select a11.location_id AS location_id, a11.pcamp_id AS pcamp_id, sum(a11.clicks) from parent_fact a11 where a11.etime in (select a12.etime from trans_last_week a12 where a11.etime = a12.lw_etime) group by a11.location_id, a11.pcamp_id Thank you! --0-16951286-1303435578=:93484 Content-Type: text/html; charset=us-ascii

This is a good one :)


Here is a "brief" description of our issue(Postgres 9.0): 


Tables:

main fact table:

Table "public.parent_fact"

        Column        |            Type             |  

----------------------+-----------------------------+-----------

 etime                | date | not null

 pcamp_id             | integer                     | 

 location_id          | integer                     | 

 impressions          | bigint                      | 

 clicks              | int


this table partitioned by etime.


We are trying to build a report, which has last week numbers alongside with this week numbers. For example: if today is Wednesday, I want to compare daily numbers from last week 3 days (mon through wed) with this week 3 days(mon through wed).


To accomplish that, we've decided to build a transformation table, which has two columns:


 Table "public.trans_last_week"

  Column  |            Type             | Modifiers 

----------+-----------------------------+-----------

 etime    | date | 

 lw_etime | date |


So for each date(etime), we have lw_etime, which is essentially etime-7 days.


Here is the first query, which performs fine:


select    a11.location_id AS location_id,

    a11.pcamp_id AS  pcamp_id,

    sum(a11.clicks)

from    parent_fact    a11

where    a11.etime between '2011-14-18' and '2011-04-20'

group  by    a11.location_id,

    a11.pcamp_id


everything is good there -  it calculates numbers from the current week and goes to only 3 partitions to aggregate numbers. 


Here is the second query:


select    a11.location_id AS location_id,

    a11.pcamp_id AS  pcamp_id,

    sum(a11.clicks)

from    parent_fact    a11

    join    trans_last_week    a12

      on     (a11.etime = a12.lw_etime)

where    a12.etime between '2011-14-18' and '2011-04-20'

group  by    a11.location_id,

    a11.pcamp_id



Here it scans through all partitions in the parent_fact table and runs 3-4 times slower.


What was noticed, that the only case when Postgres is actually going to execute the query against the right partitions is query #1. 


Is that by design? Second query join, will also result in 3 days(3 partitions) 


This query (#3) also scans all partitions:


select    a11.location_id AS location_id,

    a11.pcamp_id AS  pcamp_id,

    sum(a11.clicks)

from    parent_fact    a11

where    a11.etime in (select a12.etime from trans_last_week    a12 where a11.etime = a12.lw_etime)

group  by    a11.location_id,

    a11.pcamp_id



Thank you!

--0-16951286-1303435578=:93484--