Search Postgresql Archives

transitive pruning optimization on the right side of a join for partition tables

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I have a situation where I cannot explicitly control the queries generated from our BI and I would like to use table partitioning.  Unfortunately the queries don't appear to be taking advantage of the table partitions because the key used to limit the query results is the joined foreign key rather than the primary key on the fact table where the check constraint lives.

For example, here you can see the constraint on one of the child tables: (queries and results have been slightly altered for better readability)
     Table "public.myfact_y2004w51"
 id                                | bigint             | not null
 recorded_on_id          | integer           | not null
Indexes:
    "myfact_y2004w51_pkey" PRIMARY KEY, btree (id)
    "myfact_y2004w51_recorded_on_id" btree (recorded_on_id)
Check constraints:
    "myfact_y2004w51_recorded_on_id_check" CHECK (recorded_on_id >= 1812 AND recorded_on_id <= 1818)
Inherits: myfact

This query does a sequence scan and append across all the partition tables:
select "dates"."date_description" FROM "myfact" as "myfact", "dates" as "dates" where "myfact"."recorded_on_id" = "dates"."recorded_on_id" and "dates"."recorded_on_id" IN ('4617', '4618', '4619', '4620', '4621', '4622', '4623', '4624', '4625', '4626', '4627', '4628', '4629', '4630', '4631', '4632', '4633', '4634', '4635', '4636', '4637', '4638', '4639', '4640', '4641', '4642', '4643', '4644', '4645', '4646', '4647');

Whereas this query correctly uses just the partition tables whose check constraints specify id ranges that match the ids in the IN list: (notice the subtle difference is the "dates"."recorded_on_id" IN vs. "myfact"."recorded_on_id" IN):
select "dates"."date_description" FROM "myfact" as "myfact", "dates" as "dates" where "myfact"."recorded_on_id" = "dates"."recorded_on_id" and "myfact"."recorded_on_id" IN ('4617', '4618', '4619', '4620', '4621', '4622', '4623', '4624', '4625', '4626', '4627', '4628', '4629', '4630', '4631', '4632', '4633', '4634', '4635', '4636', '4637', '4638', '4639', '4640', '4641', '4642', '4643', '4644', '4645', '4646', '4647');

Once again I reiterate that I don't have control over the query construction and I am currently running postgresql 9.1.5.  My question is, does postgresql support transitive pruning optimization on the right side of a join for partition tables?  If so, how do I get that to work?  If not, are there plans for this and when should a release with this feature be expected?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux