Re: Optimizer Selecting Incorrect Index

David Price wrote:
> I have 2 servers both with the exact same data, the same O.S., the same
> version of Postgres (7.4.5) and the exact same db schema's (one production
> server, one development server).  One server is using the correct index for
> SQL queries resulting in extremely slow performance, the other server is
> properly selecting the index to use and performance is many times better.  I
> have tried vacuum, but that did not work.  I finally resorted to dumping the
> data, removing the database completely, creating a new database and
> importing the data only to have to problem resurface.  The table has
> 5,000,000+ rows on both the systems.
>
> When I run 'analyze verbose' on the correctly working system, the following
> is displayed:

EXPLAIN ANALYZE is usually considered enough

>  Index Scan using trptserc on trans  (cost=0.00..465.10 rows=44 width=118)
>    Index Cond: (trn_patno = 199999)
>    Filter: ((trn_old_date >= '1994-08-23'::date) AND (trn_old_date <=
> '2004-08-23'::date) AND (trn_bill_inc = 'B'::bpchar))
> (687 rows)
>
>
> Now, when I run 'analyze verbose' on the INCORRECTLY working system, the
> following is displayed:

>  Index Scan using todate on trans  (cost=0.00..105165.74 rows=1 width=143)
>    Index Cond: ((trn_old_date >= '1994-08-23'::date) AND (trn_old_date <=
> '2004-08-23'::date))
>    Filter: ((trn_patno = 199999) AND (trn_bill_inc = 'B'::bpchar))
> (713 rows)

These queries are different. The first returns 687 rows and the second
713 rows. You need to check your systems if they are supposed to be
identical.

Things to check:
1. postgresql.conf settings match - different costs could cause this
2. statistics on the two columns (trn_patno,trn_old_date) - if they
differ considerably between systems that would also explain it.

I suspect the second one, at a wild guess the working system happens to
know 199999 is fairly rare wheras the second just estimates an average.

If the stats don't help, people are going to want to see the entire
query+plan. Could you repost with the query and explain analyse on both
system. Oh, and some idea on how many rows/unique values are involved in
the important columns.

--
   Richard Huxton
   Archonet Ltd