Csajl,
> 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.
<snip>
> classifieds_dual_idx is the btree index on (class_type_id, areacode)
> and site_cm_areacode_idx is the btree index on (site_id) only.
> there is an index in the areacode table that has both (site_id, areacode)
> but it's apparently not being used. would it help the query to use that
> index instead?
No.
From the look of things, it's not the index scan that's taking time ... it's
the subplan, which is doing 61,000 loops. Which is normal for IN, but not
for EXISTS. You run VACUUM ANALYZE?
--
Josh Berkus
Aglio Database Solutions
San Francisco