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

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              |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+