Обсуждение: Weird query execution paths, ignoring indexes...
Can anyone explain this to me? It's driving me nuts. We've been
trying to optimize our database lately, and have been rewriting
queries to be more efficient and what not, and ran into this:
classifieds=# explain
classifieds-# select distinct r.main
classifieds-# from clas_region r,
classifieds-# clas_category c,
classifieds-# clas_ad a
classifieds-# where c.paperid = 20
classifieds-# and a.paperid = c.paperid
classifieds-# and a.categoryid=c.categoryid
classifieds-# and r.regionid = c.regionid;
NOTICE: QUERY PLAN:
Unique (cost=1227.30..1227.81 rows=20 width=84)
-> Sort (cost=1227.30..1227.30 rows=203 width=84)
-> Nested Loop (cost=129.13..1219.54 rows=203 width=84)
-> Merge Join (cost=129.13..269.36 rows=173 width=60)
-> Index Scan using clas_region_pkey on clas_region r
(cost=0.00..116.78 rows=1704 width=24)
-> Sort (cost=129.13..129.13 rows=173 width=36)
-> Seq Scan on clas_category c
(cost=0.00..122.71 rows=173 width=36)
-> Index Scan using idx_test on clas_ad a (cost=0.00..5.48
rows=2 width=24)
classifieds=# explain
classifieds-# select distinct s.main
classifieds-# from clas_section s,
classifieds-# clas_category c,
classifieds-# clas_ad a
classifieds-# where c.paperid = 20
classifieds-# and a.paperid = c.paperid
classifieds-# and a.categoryid=c.categoryid
classifieds-# and s.sectionid = c.sectionid;
NOTICE: QUERY PLAN:
Unique (cost=730.49..730.70 rows=8 width=84)
-> Sort (cost=730.49..730.49 rows=82 width=84)
-> Nested Loop (cost=129.13..727.87 rows=82 width=84)
-> Merge Join (cost=129.13..341.14 rows=70 width=60)
-> Index Scan using clas_section_pkey on clas_section s
(cost=0.00..177.55 rows=2585 width=24)
-> Sort (cost=129.13..129.13 rows=173 width=36)
-> Seq Scan on clas_category c
(cost=0.00..122.71 rows=173 width=36)
-> Index Scan using idx_test on clas_ad a (cost=0.00..5.48
rows=2 width=24)
As you can see, these queries are *identical* except for where
clas_section and clas_region appear. What confuses me, is that
clas_region actually has *less* rows than clas_section; otherwise
clas_region and clas_section are also identical tables. The
database is freshly vacuum and vacuum analyze'd, too.
What's really frustrating, is that all of the columns referenced in
both of these queries are *ALL INDEXED* on every table in question!
It shouldn't even be touching the tables until it freaking runs out
of indexes to scan! This is postgresql 7.1.1, which had some optimizer
fixes I wanted in place, but it looks like there are still issues.
Can anyone shed light on this?
--
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
| Shaun M. Thomas INN Database Programmer |
| Phone: (309) 743-0812 Fax : (309) 743-0830 |
| Email: sthomas@townnews.com AIM : trifthen |
| Web : hamster.lee.net |
| |
| "Most of our lives are about proving something, either to |
| ourselves or to someone else." |
| -- Anonymous |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
On Fri, 25 May 2001, Shaun Thomas wrote: > classifieds=# explain > classifieds-# select distinct r.main > classifieds-# from clas_region r, > classifieds-# clas_category c, > classifieds-# clas_ad a > classifieds-# where c.paperid = 20 > classifieds-# and a.paperid = c.paperid > classifieds-# and a.categoryid=c.categoryid > classifieds-# and r.regionid = c.regionid; > NOTICE: QUERY PLAN: > > Unique (cost=1227.30..1227.81 rows=20 width=84) > -> Sort (cost=1227.30..1227.30 rows=203 width=84) > -> Nested Loop (cost=129.13..1219.54 rows=203 width=84) > -> Merge Join (cost=129.13..269.36 rows=173 width=60) > -> Index Scan using clas_region_pkey on clas_region r > (cost=0.00..116.78 rows=1704 width=24) > -> Sort (cost=129.13..129.13 rows=173 width=36) > -> Seq Scan on clas_category c > (cost=0.00..122.71 rows=173 width=36) > -> Index Scan using idx_test on clas_ad a (cost=0.00..5.48 > rows=2 width=24) > > > classifieds=# explain > classifieds-# select distinct s.main > classifieds-# from clas_section s, > classifieds-# clas_category c, > classifieds-# clas_ad a > classifieds-# where c.paperid = 20 > classifieds-# and a.paperid = c.paperid > classifieds-# and a.categoryid=c.categoryid > classifieds-# and s.sectionid = c.sectionid; > NOTICE: QUERY PLAN: > > Unique (cost=730.49..730.70 rows=8 width=84) > -> Sort (cost=730.49..730.49 rows=82 width=84) > -> Nested Loop (cost=129.13..727.87 rows=82 width=84) > -> Merge Join (cost=129.13..341.14 rows=70 width=60) > -> Index Scan using clas_section_pkey on clas_section s > (cost=0.00..177.55 rows=2585 width=24) > -> Sort (cost=129.13..129.13 rows=173 width=36) > -> Seq Scan on clas_category c > (cost=0.00..122.71 rows=173 width=36) > -> Index Scan using idx_test on clas_ad a (cost=0.00..5.48 > rows=2 width=24) > > As you can see, these queries are *identical* except for where > clas_section and clas_region appear. What confuses me, is that > clas_region actually has *less* rows than clas_section; otherwise > clas_region and clas_section are also identical tables. The > database is freshly vacuum and vacuum analyze'd, too. > What's really frustrating, is that all of the columns referenced in > both of these queries are *ALL INDEXED* on every table in question! > It shouldn't even be touching the tables until it freaking runs out > of indexes to scan! This is postgresql 7.1.1, which had some optimizer > fixes I wanted in place, but it looks like there are still issues. The only sequence scan I see in the explain output is clas_category. How many rows does it have, and how many really match paperid=20 (The estimate seems to be 173)? What does it show for explain output if you set enable_seqscan to off?