Обсуждение: Issue with partition elimination

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!

On 4/21/11 6:26 PM, Paul Pierce wrote:
> 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)

Partition elimination currently can only handle constants and
expressions which are equivalent to constants.  It will not filter on
Joins successfully.

This will improve somewhat in 9.1, possibly enough to fix your case.
Please test this on 9.1a5 and see how well it works, and give us feedback.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com