X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id DCB0A4758F1 for ; Mon, 12 May 2003 20:47:10 -0400 (EDT) Received: from web40312.mail.yahoo.com (web40312.mail.yahoo.com [66.218.78.91]) by postgresql.org (Postfix) with SMTP id C6ADE474E42 for ; Mon, 12 May 2003 20:47:06 -0400 (EDT) Message-ID: <20030513004710.58428.qmail@web40312.mail.yahoo.com> Received: from [216.103.85.15] by web40312.mail.yahoo.com via HTTP; Mon, 12 May 2003 17:47:10 PDT Date: Mon, 12 May 2003 17:47:10 -0700 (PDT) From: csajl Subject: Re: [repost] partial index / funxtional idx or bad sql? To: josh@agliodbs.com, pgsql-performance@postgresql.org In-Reply-To: <200305121713.38449.josh@agliodbs.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Spam-Status: No, hits=-26.0 required=5.0 tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, REPLY_WITH_QUOTES autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200305/139 X-Sequence-Number: 1918 hi josh. 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. cmdb=# EXPLAIN ANALYZE cmdb-# select c.class_id, c.areacode, c.title from classifieds c cmdb-# where c.class_cat_id = '1' cmdb-# and c.areacode IN ( cmdb(# select areacode from cm_areacode where site_id = '10') cmdb-# ; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using classifieds_dual_idx on classifieds c (cost=0.00..26622.14 rows=1837 width=39) (actual time=345.48..2305.04 rows=8460 loops=1) Index Cond: (class_cat_id = 1) Filter: (subplan) SubPlan -> Materialize (cost=3.46..3.46 rows=4 width=2) (actual time=0.00..0.01 rows=5 loops=61966) -> Index Scan using site_cm_areacode_idx on cm_areacode (cost=0.00..3.46 rows=4 width=2) (actual time=0.14..0.22 rows=5 loops=1) Index Cond: (site_id = 10) Total runtime: 2314.14 msec (8 rows) ---------------------------------- 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? thanks for your help. --- Josh Berkus wrote: > 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. __________________________________ Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. http://search.yahoo.com