Re: [repost] partial index / funxtional idx or bad sql?
От | csajl |
---|---|
Тема | Re: [repost] partial index / funxtional idx or bad sql? |
Дата | |
Msg-id | 20030513035854.78339.qmail@web40310.mail.yahoo.com обсуждение исходный текст |
Ответ на | Re: [repost] partial index / funxtional idx or bad sql? (Josh Berkus <josh@agliodbs.com>) |
Список | 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
В списке pgsql-performance по дате отправления:
Предыдущее
От: Stephan SzaboДата:
Сообщение: Re: [repost] partial index / funxtional idx or bad sql?