Re: slow IN() clause for many cases

On Tue, 2005-11-29 at 17:21 -0500, Tom Lane wrote:
> Simon Riggs <simon(at)2ndquadrant(dot)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