Skip site navigation (1) Skip section navigation (2)

Re: slow IN() clause for many cases

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: andrew(at)supernews(dot)com, pgsql-hackers(at)postgresql(dot)org
Subject: Re: slow IN() clause for many cases
Date: 2005-11-29 22:21:28
Message-ID: 10581.1133302888@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
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.

> 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


In response to

Responses

pgsql-hackers by date

Next:From: Simon RiggsDate: 2005-11-29 22:23:05
Subject: Re: ice-broker scan thread
Previous:From: Simon RiggsDate: 2005-11-29 22:19:27
Subject: Re: ice-broker scan thread

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group