SELECT performance drop v 6.5 -> 7.0.3

Hi!

We've been very satisfied customers of Postgres 6.5 for the past 12 months.
Our bioinformatics database is growing currently with around 100 tables and
a few dozen million records.
Our only gripe was to do with the rather slow importing of data using 'COPY
FROM' (36 hours for the above db on a 533Mhz Pentium III, Linux
2.2.17-21mdk/Mandrake 7.2, 256Mb memory). Otherwise in a friendly
performance competition with our industrial partners who share exactly the
same interface code but with an Oracle backend, we were rather proud
Postgres proponents - and slightly the richer.

So we decided to upgrade to Pg 7.0.2 wondering if we'd see faster data
import. Indeed the same total import was achieved in around 6 hours on the
same machine! However SELECT statements over big table joins that
previously took an instant (a few seconds at most) under v6.5 now took so
long our web interfaces would time out (minutes at least, we aborted rather
than wait)... We tried Linux RPM's for v7.0.2 and 7.0.3, as well as gcc
(v2.95.3) compiled these versions on our machine, but to no avail. We also
noticed strong incidence on performance depending on whether we built the
indexes before import straight after creating the tables (very very slow
SELECTS), or after data import (quicker import+index build but yet even
slower SELECTS, I don't think we were ever patient enough to wait for a
SELECT result).

We've reluctantly switched back to v6.5 in order to recover a workable db
interface. We can just about still handle a db update over the week end...
However with the current rate of data growth, this will very soon prove
impossible. We also regret not being able to take advantage of the
significant other improvements made in v7. Above all we're mystified by
what has happended in v7 and how come we seem the only ones to notice the
change. Maybe we're the only ones silly enough to think such a hefty db can
live on a modest Linux box...

Any suggestions? Anyone else noticed this dramatic change from v6.5 to v7.0.3?
Cheers,
Pascal
---------------------------------------------------------------------------
Pascal Hingamp                                     hingamp@ciml.univ-mrs.fr
Techniques Avancées pour le Génome et la Clinique   http://tagc.univ-mrs.fr
Centre d'Immunologie de Marseille Luminy          mob: (+33) 06 17 53 18 80
Parc Scientifique de Luminy, Case 906             lab: (+33) 04 91 26 94 96
13288 Marseille Cedex 09, FRANCE                  Fax: (+33) 04 91 26 94 30
---------------------------------------------------------------------------