Performance degradation in PostgreSQL 7.1beta3 vs 6.5.3

Dear Tom,
I am writing to you because you are the maintainer of the
query optimizer and planner.
I have found a very significant performance degradation
between PostgreSQL 6.5.3 and 7.1beta3, which will severely impact two
large applications that I have developed and maintain for several
clients. The performance difference is seen with the use of indices in
SELECT statements, whereby the current release does not make effective
use of the indices and 6.5.3 does. All of these tests were run on a SGI
R10000 Indigo2 system running Irix 6.5. All the regression tests passed
as expected for both versions.
I have followed the discussion in pgsql-hackers over the previous
months and others have noted some performance problems, and the response
has typically been to VACUUM the tables. Unfortunately, this is not a
practical option for my applications. They are very large -- I have one
table that is 17GB in length, and the applications are used frequently.
More importantly, PostgreSQL 6.5.3 works very, very well without
VACUUM'ing.
In order to assist you to diagnosing and correcting this
problem, I have prepared a test database that shows the problems. I
will attach three files; the test script, the log from running it on
version 6.5.3, and the log from running it on version 7.1beta3. In
addition, I have setup an anonymous FTP directory on
ftp.congen.com:/pub/pg_perf which contains all of these files as well
as the compressed table dumps used to build the test database. (When
you have finished copying the files, please let me know.)
The test script creates the database including the necessary
indexing, and then runs EXPLAIN on each of the queries followed by
actually executing the queries with "timex" commands to report elapsed
times. The striking difference in the query plans is that 7.1 uses
only sequential searches for the SELECT's whereas 6.5.3 uses index
scans. As a result, 7.1 is almost two orders of magnitude slower than
6.5.3 with exactly the same data, schema, and queries.

I plead with you to revisit this question of performance and
fix PostgreSQL 7.1 to work as well as PostgreSQL 6.5.3 does. I depend
upon PostgreSQL for much of my work, and I do not want to abandon it
because of this performance problem which arose only recently. Thank
you.

+----------------------------------+------------------------------------+
| Robert E. Bruccoleri, Ph.D. | Phone: 609 737 6383 |
| President, Congenomics, Inc. | Fax: 609 737 7528 |
| 114 W Franklin Ave, Suite K1,4,5 | email: bruc(at)acm(dot)org |
| P.O. Box 314 | URL: http://www.congen.com/~bruc |
| Pennington, NJ 08534 | |
+----------------------------------+------------------------------------+