From: "Tomasz Myrta" <jasiek@klaster.net>
To: "James Cooper" <jim@luckydigital.com>,
	"sql" <pgsql-sql@postgresql.org>
Subject: Re: INTERSECT / where id IN (etc..)
Date: Thu, 6 Mar 2003 07:39:53 +0800
Message-Id: <20030306073953.M77564@klaster.net>
In-Reply-To: <003701c2e37a$fe0bb2d0$c900a8c0@jax>
References: <003701c2e37a$fe0bb2d0$c900a8c0@jax>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-2

> Greetings.
> 
> I was wondering if somone out there could shed some light on a query 
> performance issie I have. I have a large lookup table that I query 
> with nested selects to gain the INTERSECT result from.
> 
> the bigger this table gets the slower the performance.
> 
> 1. is creating a temp table and only intersecting from this a good idea?
> 
> 2. does having a where clause in your select before your nested 
> intersects change the amount of work the query
> 
> i.e. select person_id from person where person_id < 5000 And 
> person_id IN    as opposed to    select person_id from person where 
> person_id IN(etc)
> 
> thoughts?
person_id<5000 probably won't help - postgres will use index on exact 
person_id if possible.
Anyway - for large queries try change IN (...) into EXISTS (...), or just 
into explicit join if possible. IN clause is rather slow for bigger amount of 
data.

Regards,
Tomasz Myrta