X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id EC929475EEE for ; Mon, 12 May 2003 20:07:43 -0400 (EDT) Received: from web40301.mail.yahoo.com (web40301.mail.yahoo.com [66.218.78.80]) by postgresql.org (Postfix) with SMTP id 024044758F1 for ; Mon, 12 May 2003 20:07:43 -0400 (EDT) Message-ID: <20030513000746.70481.qmail@web40301.mail.yahoo.com> Received: from [216.103.85.15] by web40301.mail.yahoo.com via HTTP; Mon, 12 May 2003 17:07:46 PDT Date: Mon, 12 May 2003 17:07:46 -0700 (PDT) From: csajl Subject: [repost] partial index / funxtional idx or bad sql? To: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Spam-Status: No, hits=-5.8 required=5.0 tests=BAYES_10 version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200305/137 X-Sequence-Number: 1916 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