my apologies - a strange key combination sent the message early.
----
greetings.
i have a query that is taking a rather long time to execute and have been
looking into setting up a partial index to help, although i'm not sure if this
is what i want.
here is the (simplified) table "posts":
id serial
type_id int
areacode smallint
content text
and the other table (areacodes) referenced:
site_id smallint
areacode smallint
the query is:
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 = ?
)
the "posts" table has 100,000 rows of varying data, across areacodes and types.
given the type_id and site_id, the query is currently taking ~4 seconds to
return 8500 rows (on a dual proc/ gig ram linux box).
indexes on table "posts" are:
primary key (id)
and another on both (type_id, areacode)
index on the table "areacodes" is (site_id, areacode).
would a parital index help in speeding up this query?
are my current indexes counter productive?
or is it just my sql that need help?
thanks much for any help or pointers to information.
- seth
__________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com