Re: Weird query execution paths, ignoring indexes...

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?