Re: slow IN() clause for many cases

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.86rows=10 width=244)  Recheck Cond: (unique1 = ANY ($0))  InitPlan    ->  Seq Scan on int4_tbl
(cost=0.00..1.05rows=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.

> 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.
        regards, tom lane