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

On 30 Sep 2012, at 8:36, Waldo, Ethan wrote:

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

Don't be too hasty making assumptions there! ;)

> 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');

What does EXPLAIN ANALYSE for these queries show? (might take a while, it performs the actual query)
My guess is that there will be a large difference in selectivity between both tables for those ID's.

Those id's, seeing that you're partitioning on them and they're in a year/week table, do those numbers have some meaning? Or is it perhaps just a daily increment that happens to have some sort of correlation to the date? Without more information, it seems a peculiar column to use for partitioning.

It's possible that the issue here is just related to planner statistics, but it's also possible that it's necessary to change your partitioning to aid your BI tools.
Another possibility is submitting a case with the people behind that BI software.

Alban Hertroys

--
The scale of a problem often equals the size of an ego.