8.3devel slower than 8.2 under read-only load

From: "Guillaume Smet" <guillaume(dot)smet(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: 8.3devel slower than 8.2 under read-only load
Date: 2007-11-21 19:04:29
Message-ID: 1d4e0c10711211104m2eff1a09sf8a5e3234c21d6f2@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi -hackers,

I'm currently testing 8.3devel on the database of one of our customers
(4 GB database used by a website - mostly read only activity). My main
concern was to discover if there is any query choosing a bad plan in
8.3 for one reason or another.
While I didn't find anything far slower than before yet, the time
needed to generate pages with 8.3 is consistently a little higher than
with 8.1 or 8.2. I have a debug interface giving the execution time of
each query and they are all a bit slower with 8.3. When you have a lot
of queries on a page, it becomes noticeable.

I took a couple of very simple read only queries executed in the pages
to create a pgbench script and I have the following results:
*** 8.2 ***
[ip-dyn151 postgresql]# pgbench -h localhost -p 5482 -U cityvox -f
bench.cityvox.simple.sql -t 1000 cityvox
starting vacuum...end.
transaction type: Custom query
scaling factor: 1
number of clients: 1
number of transactions per client: 1000
number of transactions actually processed: 1000/1000
tps = 853.360277 (including connections establishing)
tps = 855.792905 (excluding connections establishing)

*** 8.3 ***
[ip-dyn151 postgresql]# pgbench -h localhost -p 5483 -U cityvox -f
bench.cityvox.simple.sql -t 1000 cityvox
starting vacuum...end.
transaction type: Custom query
scaling factor: 1
number of clients: 1
number of transactions per client: 1000
number of transactions actually processed: 1000/1000
tps = 784.819087 (including connections establishing)
tps = 786.884214 (excluding connections establishing)

All the queries are index scans (SELECT a couple of fields FROM a
table WHERE one or two conditions). I checked the plans on both 8.2
and 8.3 and they are identical. I made several runs and numbers are
consistent.

I used the default ./configure options, the configuration is identical
for both versions, locale is fr_FR.UTF8 and it's a Unicode database.
Both are compiled with the same compiler (it's a CentOS 5 box).

Is this something expected?

While I'm not so worried by these figures for our other databases,
this database in particular is highly loaded with a *lot* of read only
queries and I'm not sure we can afford this sort of performance drop.
I can provide any additional information needed or run further tests
without any problem so feel free to ask.

Thanks.

--
Guillaume

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Brandon Maust 2007-11-21 19:23:39 Re: plperl failure on OS X 10.5(.1)
Previous Message Kevin Grittner 2007-11-21 18:55:49 Re: VACUUM/ANALYZE counting of in-doubt tuples