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
