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

"Waldo, Ethan" <ewaldo(at)healthetechs(dot)com> writes:
> 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');

When I try that in 9.1, I get a plan with inner indexscans for each
child table; which, while not exactly what you're asking for, should
perform well enough when the fact table is large enough that
partitioning is actually a useful activity.

I suspect you're committing one of the ten deadly sins of Postgres
optimization, which is to assume that the plan you get on a toy test
case is the same plan you'd get for monster tables. Planning choices
are nonlinear.

regards, tom lane