X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 3A67C4758F1 for ; Mon, 12 May 2003 23:58:50 -0400 (EDT) Received: from web40310.mail.yahoo.com (web40310.mail.yahoo.com [66.218.78.89]) by postgresql.org (Postfix) with SMTP id 2C38A474E42 for ; Mon, 12 May 2003 23:58:49 -0400 (EDT) Message-ID: <20030513035854.78339.qmail@web40310.mail.yahoo.com> Received: from [216.103.85.15] by web40310.mail.yahoo.com via HTTP; Mon, 12 May 2003 20:58:54 PDT Date: Mon, 12 May 2003 20:58:54 -0700 (PDT) From: csajl Subject: Re: [repost] partial index / funxtional idx or bad sql? To: Josh Berkus , pgsql-performance@postgresql.org In-Reply-To: <200305122032.10928.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/143 X-Sequence-Number: 1922 hi josh. thanks for your help and time with this. ran vacuum analyze, still timed in around 3seconds. i dropped the site_id only index on the areacodes table in favor of the dual site_id and areacode index and seemingly gained 1/2 second. by using the IN, i gain another .3 of a second. (i thought EXISTS was supposed to be more efficient?) the loop on the subplan (~62k) is killing me. any alternatives to what i thought would be a seemingly innocuous lookup? the cm_Areacode table is nothing more than two columns, associating each areacode into a site_id. (292 rows if i remember correctly) cmdb=# EXPLAIN ANALYZE cmdb-# select c.class_id, c.areacode, c.title from classifieds c cmdb-# where c.class_cat_id = '1' cmdb-# and EXISTS ( cmdb(# select areacode from cm_areacode cm where site_id = '10' and c.areacode = cm.areacode) cmdb-# ; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using classifieds_dual_idx on classifieds c (cost=0.00..493277.77 rows=28413 width=39) (actual time=360.23..2523.08 rows=8460 loops=1) Index Cond: (class_cat_id = 1) Filter: (subplan) SubPlan -> Index Scan using areacode_site_dual_cmareacode on cm_areacode cm (cost=0.00..4.96 rows=1 width=2) (actual time=0.01..0.01 rows=0 loops=61966) Index Cond: ((site_id = 10) AND ($0 = areacode)) Total runtime: 2533.93 msec (7 rows) cmdb=# ------------------------------------ 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..632183.80 rows=28413 width=39) (actual time=344.70..2287.93 rows=8460 loops=1) Index Cond: (class_cat_id = 1) Filter: (subplan) SubPlan -> Materialize (cost=7.40..7.40 rows=4 width=2) (actual time=0.00..0.00 rows=5 loops=61966) -> Seq Scan on cm_areacode (cost=0.00..7.40 rows=4 width=2) (actual time=0.20..0.73 rows=5 loops=1) Filter: (site_id = 10) Total runtime: 2296.83 msec (8 rows) --- Josh Berkus wrote: > Csajl, > > > 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. > > > 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? > > No. > From the look of things, it's not the index scan that's taking time ... it's > the subplan, which is doing 61,000 loops. Which is normal for IN, but not > for EXISTS. You run VACUUM ANALYZE? __________________________________ Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. http://search.yahoo.com