Re: slow IN() clause for many cases

[Greg Stark <gsstark(at)mit(dot)edu>]

Simon Riggs <simon(at)2ndquadrant(dot)com> writes:

> IMHO the only way to do joins that access partitions is to do the
> constraint exclusion at run time, but I can see thats a longer
> conversation than I can start right now.

My experience in Oracle was that you can see three different types of
partition plans

1) Plans that the planner can statically deduce will access specific
   partitions. This is basically what Postgres has now.

2) Plans that the planner can statically deduce will only access a limited
   number of partitions but which partitions will be determined at run-time.
   This is sort of like how Postgres handles index scans in subqueries where
   the value being looked up is a parameter.

3) Plans that expect to span all the partitions because it can't find any
   constraint in the query from which it can deduce which partitions to look
   at.

Option 2 happens both for joins, and also for simple lookups where the value
being looked up is a parameter in a prepared query. My personal inclination is
that prepared queries are the way of the future so I think this is an
important case.

-- 
greg