Re: [repost] partial index / funxtional idx or bad sql? - Mailing list pgsql-performance

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