pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: csajl <csajl@yahoo.com>
To: Josh Berkus <josh@agliodbs.com>
To: pgsql-performance@postgresql.org
Subject: Re: [repost] partial index / funxtional idx or bad sql?
Date: Mon, 12 May 2003 20:58:54 -0700 (PDT)
Message-ID: <20030513035854.78339.qmail@web40310.mail.yahoo.com> (raw)
In-Reply-To: <200305122032.10928.josh@agliodbs.com>


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 <josh@agliodbs.com> 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.
> <snip>
> > 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




view thread (10+ messages)  latest in thread

Message-ID: <20030513035854.78339.qmail@web40310.mail.yahoo.com>
Permalink:  ../20030513035854.78339.qmail@web40310.mail.yahoo.com/
Also on:    postgresql.org/message-id/20030513035854.78339.qmail@web40310.mail.yahoo.com

 · 

reply

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, josh@agliodbs.com
  Subject: Re: [repost] partial index / funxtional idx or bad sql?
  In-Reply-To: <20030513035854.78339.qmail@web40310.mail.yahoo.com>
  References: <200305122032.10928.josh@agliodbs.com> <20030513035854.78339.qmail@web40310.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