Re: [repost] partial index / funxtional idx or bad sql? - Mailing list pgsql-performance

On Mon, 12 May 2003, csajl wrote:

> i'm using 7.3.2.  i tried using EXISTS instead of the IN, but the same query
> now returns in seven sceonds as opposed to four with the IN.
>
>
> cmdb=# EXPLAIN ANALYZE
> cmdb-# select c.class_id, c.areacode, c.title from classifieds c
> cmdb-# where c.class_cat_id = '1'
> cmdb-# and c.areacode IN (
> cmdb(# select areacode from cm_areacode where site_id = '10')
> cmdb-# ;

How about something like:

select c.class_id, c.areacode, c.title from
 classifieds c,
 (select distinct areacode from cm_areacode where site_id='10') a
 where c.class_cat_id='1' and c.areacode=a.areacode;