[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] transitive pruning optimization on the right side of a join for partition tables
From:       Tom Lane <tgl () sss ! pgh ! pa ! us>
Date:       2012-09-30 15:14:31
Message-ID: 25559.1349018071 () sss ! pgh ! pa ! us
[Download RAW message or body]

"Waldo, Ethan" <ewaldo@healthetechs.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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[prev in list] [next in list] [prev in thread] [next in thread]