Re: slow IN() clause for many cases

On Tue, 2005-11-29 at 17:21 -0500, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > Do you think we'll be able to generate a single ScalarArrayOpExpr from a
> > small subselect and pass it through as an indexable expression?
> 
> If you don't mind spelling it with the ARRAY(sub-select) syntax, which
> I think is a Postgres-ism (though it's possible Joe got it from
> SQL2003).
> 
> regression=# explain select * from tenk1 where unique1 = any (array(select f1 from int4_tbl));
>                                  QUERY PLAN
> -----------------------------------------------------------------------------
>  Bitmap Heap Scan on tenk1  (cost=3.09..37.86 rows=10 width=244)
>    Recheck Cond: (unique1 = ANY ($0))
>    InitPlan
>      ->  Seq Scan on int4_tbl  (cost=0.00..1.05 rows=5 width=4)
>    ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.04 rows=10 width=0)
>          Index Cond: (unique1 = ANY ($0))
> (6 rows)
> 
> Of course the planner is just guessing about how many rows this will
> produce.

So we could teach the planner to transform:

IN (subselect) 

into

= ANY(array(subselect))

if we had the planner think the subselect had say < 1000 rows?

> > e.g.
> > Select * From Sales where month IN (
> > select month from time_dimension where FinYear = 2005 and Quarter = 3)
> 
> > Having taught predtest.c about ScalarArrayOpExpr means that would allow
> > this to work with constraint exclusion.
> 
> Not hardly, unless you want to play fast and loose with semantics by
> evaluating subselects at plan time instead of run time.  You could
> persuade that to happen by wrapping the ARRAY(sub-select) into a
> function mis-declared as IMMUTABLE, but I'd be pretty resistant to
> having the planner assume any such thing by default.

Man, thats a horrible thought. I must be dragging you down :-)

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.

Best Regards, Simon Riggs