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

I don't need help troubleshooting the problem.  I know exactly what the problem is.  Either you know if postgresql
supportstransitive pruning optimization on the right side of a join via a check constraint defined on a table partition
oryou don't.  If you do know, I'd appreciate knowledgeable input in that regard.  Thank you for your response. 

----- Original Message -----
From: "Alban Hertroys" <haramrae@gmail.com>
Sent: Sun, 9/30/2012 4:51am
To: "Waldo, Ethan" <ewaldo@healthetechs.com>
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 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
usedto limit the query results is the joined foreign key rather than the primary key on the fact table where the check
constraintlives.  

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