Re: Weird query execution paths, ignoring indexes... - Mailing list pgsql-general

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?