Re: [repost] partial index / funxtional idx or bad sql?

Seth,

> SELECT p.id, p.areacode, p.content
> FROM posts p
> WHERE p.type_id = ?
> AND p.areacode in (
>   select areacode from areacodes
>    where site_id = ?
>  )

Unless you're using 7.4 from CVS, you want to get rid of that IN:

 SELECT p.id, p.areacode, p.content
 FROM posts p
 WHERE p.type_id = ?
 AND EXISTS (
  select areacode from areacodes
    where site_id = ?
    and p.areacode = areacodes.areacode
  );

See how that works, and if it's still slow, post the EXPLAIN ANALYZE.


--
-Josh Berkus
 Aglio Database Solutions
 San Francisco