I'm getting poor performance on 7.1 as compared to 7.0. For 7.1, I'm
using the same setup as I had with 7.0. I use -F (disable fsync) and
have set postgres up with over 300MB of shared memory; that stuff
stayed consistent across the transition. I've got two 2.2 linux
machines, one setup with 7.0 and the other 7.1.
My queries involve doing a BETWEEN search on 30 values on a table that
has a just over a million rows:
SELECT metadata_id FROM datapoints
WHERE data1 BETWEEN 885::int2 AND 3819::int2 AND
data2 BETWEEN 2766::int2 AND 4222::int2 AND
data3 BETWEEN 2962::int2 AND 3924::int2 AND
...
data30 BETWEEN -943::int2 AND 1569::int2;
I've got a btree index on the first 16 of the data elements, and
explain verifies that the index is used.
Using 7.1, my queries are running more than twice as slow as 7.0.
I've got a test set of 10,000 queries that I hit the database with,
each element in the 10k performs a SELECT query like the one described
above with varying data. Here are the search times I'm getting with
the different postgres versions:
search time stats for 10k queries (seconds)
pg
version mean std dev max min
7.0 0.33 0.16 1.89 0.01
7.1 0.79 0.38 3.47 0.02
These are the stats on a single query, which are performed in serial
with no other users/connections on either machine. I'm not concerned
about the max/min numbers, but the fact that the mean for 7.1 is well
over twice that of 7.0 troubles me.
While the added features in 7.1 are excellent (hats off to the
postgres team, really!), if a feature of 7.1 is to slow the database
down this significantly then I'll have to stick with 7.0. I'm open to
suggestions as to what to try here-- and yes, I have vacuum analyzed
this database... I can quickly & easily perform tests here, I've got
the datasets in pgdump files and have the two machines at my disposal.
Regards,
Kyle
kaf@_nwlink_._com_ =~ s/_//g;
