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

Re: IN subselects and index / seq_scan question....

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Marc Mitchell" <marcm(at)eisolution(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: IN subselects and index / seq_scan question....
Date: 2002-11-19 03:43:52
Message-ID: 17248.1037677432@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-admin
"Marc Mitchell" <marcm(at)eisolution(dot)com> writes:
> I am having an optimization problem with queries that include IN clauses
> and subselects.

The optimizer currently has no clue at all about dealing with
"IN (subselect)", and always converts it into the worst sort of
nested-loop plan.  I'm hoping to do something about that for 7.4, but
it's only pie-in-the-sky today.  In the meantime, the conventional
wisdom is to convert your query into an EXISTS() test instead of an IN()
test.  This is still nested-loop, but not as bad --- the clause you push
down into the EXISTS() has a shot at using an index.  I believe the
details are in the FAQ.

			regards, tom lane

In response to

pgsql-admin by date

Next:From: Bruno Wolff IIIDate: 2002-11-19 14:07:58
Subject: Re: Confused about user permissions and pg_hba.conf
Previous:From: Graham WilsonDate: 2002-11-19 01:41:34
Subject: Confused about user permissions and pg_hba.conf

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