Thread: poor performance on 7.1 vs 7.0

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;

Kyle <kaf@nwlink.com> writes:
> I'm getting poor performance on 7.1 as compared to 7.0.

Hm.  I don't know why 7.1 might be slower for plain SELECTs (the WAL
changes would certainly not affect that).  Could you compile 7.1 for
profiling ("make clean; make PROFILE=-pg all" in the src/backend
subdirectory should do it) and send along a gprof table for your
test run?

> I've got a btree index on the first 16 of the data elements, and
> explain verifies that the index is used.

Simple explain doesn't prove that the index is being fully used ---
the display doesn't distinguish how many index columns are actually
being looked at.  EXPLAIN VERBOSE output would resolve that doubt.

            regards, tom lane

When I profile the postmaster do the forked backends also show up in
the profile?  Or do I need to profile those separately?  (and how?)




Tom Lane wrote:
> Kyle <kaf@nwlink.com> writes:
> > I'm getting poor performance on 7.1 as compared to 7.0.
>
> Hm.  I don't know why 7.1 might be slower for plain SELECTs (the WAL
> changes would certainly not affect that).  Could you compile 7.1 for
> profiling ("make clean; make PROFILE=-pg all" in the src/backend
> subdirectory should do it) and send along a gprof table for your
> test run?
>
> > I've got a btree index on the first 16 of the data elements, and
> > explain verifies that the index is used.
>
> Simple explain doesn't prove that the index is being fully used ---
> the display doesn't distinguish how many index columns are actually
> being looked at.  EXPLAIN VERBOSE output would resolve that doubt.
>
>             regards, tom lane
>

To answer my own question, never mind...just read Tom's writeup on how
to get the db-specific profile:
      http://fts.postgresql.org/db/mw/msg.html?mid=32379

-Kyle, off to read more online docs


Kyle wrote:
> When I profile the postmaster do the forked backends also show up in
> the profile?  Or do I need to profile those separately?  (and how?)
>
>
>
>
> Tom Lane wrote:
> > Kyle <kaf@nwlink.com> writes:
> > > I'm getting poor performance on 7.1 as compared to 7.0.
> >
> > Hm.  I don't know why 7.1 might be slower for plain SELECTs (the WAL
> > changes would certainly not affect that).  Could you compile 7.1 for
> > profiling ("make clean; make PROFILE=-pg all" in the src/backend
> > subdirectory should do it) and send along a gprof table for your
> > test run?
> >
> > > I've got a btree index on the first 16 of the data elements, and
> > > explain verifies that the index is used.
> >
> > Simple explain doesn't prove that the index is being fully used ---
> > the display doesn't distinguish how many index columns are actually
> > being looked at.  EXPLAIN VERBOSE output would resolve that doubt.
> >
> >             regards, tom lane
> >

Kyle <kaf@nwlink.com> writes:
> When I profile the postmaster do the forked backends also show up in
> the profile?  Or do I need to profile those separately?  (and how?)

As each process (postmaster or backend) terminates, it drops a gmon.out
file in the directory it was working in.  As long as you are profiling
a backend that's alone in the database it's in, the gmon.out file in
$PGDATA/base/dbnumber/gmon.out is the one you want.

The profile results for a backend will include the CPU usage of the
postmaster up to the instant that the backend was forked off, but it's
normally easy to ignore that, particularly if you haven't been running
the profilable postmaster for very long before you do the test.

            regards, tom lane