| 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? |
