Tom Lane <tgl@sss.pgh.pa.us> writes:
> > Partition Elimination relies upon being able to prove at execution time
>
> You mean plan time.
Fwiw, both are possible.
In oracle there are (at least) three different cases:
1. For queries like "select * from tab" the plan shows a multiple partition
scan.
2. For queries like "select * from tab where partition_key = 1" the plan
shows the partition that the planner proved would suffice.
3. For queries like select * from tab where partition_key = ?" the plan
shows a single partition scan of a partition to be determined at run-time.
This last case can be very important for joins as well. For queries like
select * from tab,tab2 where tab1.partition_key = tab2.fk" the planner can
prove that each nested loop iteration will only require a single btree lookup.
This is important since if it can't prove this the nested loop has to do
multiple index lookups making it look very poor compared to a merge join or
hash join.
There are also cases where Oracle knows that the plan node will involve
reading multiple partitions but not all.
--
greg
