From: csajl <csajl@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: [repost] partial index / funxtional idx or bad sql?
Date: Mon, 12 May 2003 17:07:46 -0700 (PDT)
Message-ID: <20030513000746.70481.qmail@web40301.mail.yahoo.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: csajl@yahoo.com
Subject: Re: [repost] partial index / funxtional idx or bad sql?
In-Reply-To: <20030513000746.70481.qmail@web40301.mail.yahoo.com>
References: <20030513000746.70481.qmail@web40301.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox