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?
