Thread: Postgres 8.3 is not using indexes

Hi,

i just stumbled on something very strange.

I have here a Postgres 8.3 and a Postgres 8.2 installation, as I am in
the process of merging. Both are from the debian/testing tree, both have
the same configuration file.

In my DB where I found out this trouble I have two tables, I do a very
simple join over both. The foreign key in the second table has an index.

Postgres 8.2 gives me this out:

explain SELECT DISTINCT email  FROM email e, email_group eg WHERE
e.email_group_id = eg.email_group_i
             QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Unique  (cost=65.16..66.81 rows=85 width=27)
   ->  Sort  (cost=65.16..65.98 rows=330 width=27)
         Sort Key: e.email
         ->  Merge Join  (cost=0.00..51.35 rows=330 width=27)
               Merge Cond: (eg.email_group_id = e.email_group_id)
               ->  Index Scan using email_group_pkey on email_group eg
(cost=0.00..12.91 rows=44 width=4)
               ->  Index Scan using idx_email_email_group_id on email e
 (cost=0.00..34.21 rows=330 width=31)

Postgres 8.3 returns this:


explain SELECT DISTINCT email  FROM email e, email_group eg WHERE
e.email_group_id = eg.email_group_id;
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Unique  (cost=268688.95..274975.13 rows=51213 width=26)
   ->  Sort  (cost=268688.95..271832.04 rows=1257236 width=26)
         Sort Key: e.email
         ->  Hash Join  (cost=2.12..85452.48 rows=1257236 width=26)
               Hash Cond: (e.email_group_id = eg.email_group_id)
               ->  Seq Scan on email e  (cost=0.00..68163.36
rows=1257236 width=30)
               ->  Hash  (cost=1.50..1.50 rows=50 width=4)
                     ->  Seq Scan on email_group eg  (cost=0.00..1.50
rows=50 width=4)

I have reindexed the tables, vacuum (analyze) the whole DB, checked the
config if there are some settings different. But I am at a loss here.
Why is Postgres not using the indexes in the 8.3 installation.

I tried this on a different DB on the same server and on a different
server and I always get "seq_scan" back and never the usage of the index.

Any tips why this is so?

--
[ Clemens Schwaighofer                      -----=====:::::~ ]
[ IT Engineer/Manager                                        ]
[ E-Graphics Communications, TEQUILA\ Japan IT Group         ]
[                6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ]
[ Tel: +81-(0)3-3545-7706            Fax: +81-(0)3-3545-7343 ]
[ http://www.tequila.jp                                      ]

Am Thursday, 14. August 2008 schrieb Clemens Schwaighofer:
> Why is Postgres not using the indexes in the 8.3 installation.

Might have something to do with the removal of some implicit casts.  You
should show us your table definitions.

"Clemens Schwaighofer" <cs@tequila.co.jp> writes:

> Any tips why this is so?

They don't appear to contain the same data.
If they do have you run analyze recently?

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's PostGIS support!

This e-mail is intended only for the named person or entity to which 
it is addressed and contains valuable business information that is 
privileged, confidential and/or otherwise protected from disclosure.  
Dissemination, distribution or copying of this e-mail or the 
information herein by anyone other than the intended recipient, or 
an employee or agent responsible for delivering the message to the 
intended recipient, is strictly prohibited.  All contents are the 
copyright property of TBWA Worldwide, its agencies or a client of 
such agencies. If you are not the intended recipient, you are 
nevertheless bound to respect the worldwide legal rights of TBWA 
Worldwide, its agencies and its clients. We require that unintended 
recipients delete the e-mail and destroy all electronic copies in 
their system, retaining no copies in any media.If you have received 
this e-mail in error, please immediately notify us via e-mail to 
disclaimer@tbwaworld.com.  We appreciate your cooperation.

We make no warranties as to the accuracy or completeness of this 
e-mail and accept no liability for its content or use.  Any opinions
expressed in this e-mail are those of the author and do not 
necessarily reflect the opinions of TBWA Worldwide or any of its 
agencies or affiliates.