Re: [repost] partial index / funxtional idx or bad sql?

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(at)agliodbs(dot)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