From: Miguel Silva <miguel.silva@tactis.pt>
To: pgsql-performance@postgresql.org
Subject: Query performance - normal on 9.0.4, slow from 9.0.5 onwards
Date: Fri, 30 Dec 2011 16:39:04 +0000
Message-ID: <4EFDE928.50807@tactis.pt> (raw)
Hi all!
I've ran into a performance problem a few time ago and I've been trying
to figure out a solution until now. But since I've failed to come up
with anything conclusive, it's time to ask some help from people with
more understanding of how postgresql works.
Here's the big picture.
I work for a software company that has it's main program installed on
over 200 clients. This program uses a small local database in
postgresql. Always installed with the one-click installer and
postgresql.conf left on default settings. This structure allows us to
always install the latest version of postgresql both in new clients and
older clients (when they are updated). And all was well for over 7 years.
But with postgresql version 9.0.5 (in version 9.0.4 all was fine), we
noticed the program was taking longer to start. In fact, in some clients
that had older hardware, it could take around 20 minutes when it usually
takes only a few seconds. To make a long story short, the problem was
traced and narrowed down to a single auto generated query. Here it is:
"SELECT
NULL::text AS PKTABLE_CAT,
pkn.nspname AS PKTABLE_SCHEM,
pkc.relname AS PKTABLE_NAME,
pka.attname AS PKCOLUMN_NAME,
NULL::text AS FKTABLE_CAT,
fkn.nspname AS FKTABLE_SCHEM,
fkc.relname AS FKTABLE_NAME,
fka.attname AS FKCOLUMN_NAME,
pos.n AS KEY_SEQ,
CASE con.confupdtype WHEN 'c' THEN 0 WHEN 'n' THEN 2 WHEN 'd' THEN
4 WHEN 'r' THEN 1 WHEN 'a' THEN 3 ELSE NULL END AS UPDATE_RULE,
CASE con.confdeltype WHEN 'c' THEN 0 WHEN 'n' THEN 2 WHEN 'd' THEN
4 WHEN 'r' THEN 1 WHEN 'a' THEN 3 ELSE NULL END AS DELETE_RULE,
con.conname AS FK_NAME,
pkic.relname AS PK_NAME,
CASE WHEN con.condeferrable AND con.condeferred THEN 5 WHEN
con.condeferrable THEN 6 ELSE 7 END AS DEFERRABILITY
FROM
pg_catalog.pg_namespace pkn,
pg_catalog.pg_class pkc,
pg_catalog.pg_attribute pka,
pg_catalog.pg_namespace fkn,
pg_catalog.pg_class fkc,
pg_catalog.pg_attribute fka,
pg_catalog.pg_constraint con,
pg_catalog.generate_series(1, 32) pos(n),
pg_catalog.pg_depend dep,
pg_catalog.pg_class pkic
WHERE pkn.oid = pkc.relnamespace
AND pkc.oid = pka.attrelid
AND pka.attnum = con.confkey[pos.n]
AND con.confrelid = pkc.oid
AND fkn.oid = fkc.relnamespace
AND fkc.oid = fka.attrelid
AND fka.attnum = con.conkey[pos.n]
AND con.conrelid = fkc.oid
AND con.contype = 'f'
AND con.oid = dep.objid
AND pkic.oid = dep.refobjid
AND pkic.relkind = 'i'
AND dep.classid = 'pg_constraint'::regclass::oid
AND dep.refclassid = 'pg_class'::regclass::oid
AND pkn.nspname = 'public'
AND fkn.nspname = 'public'
ORDER BY
pkn.nspname,
pkc.relname,
pos.n;"
From this point on, in all the tests I did, I directly typed this query
on psql command line. I tried everything. Vaccuming and analyzing
(although this is already automatic on postgresql 9.0), updating
postgresql to version 9.1, tuning the database as explained on
postgresql.org documentation (with various values to every parameter,
different possible combinations), nothing worked, EXCEPT switching the
"enable_material" parameter to OFF. That reduces the query time from
around 25 seconds on my system (Intel Core2 Duo 2.93GHz 32bit running
Windows 7 Enterprise Service Pack 1) to around 5 seconds. Here are the
explain analyzes.
enable_material ON: http://explain.depesz.com/s/wen
enable_material OFF: http://explain.depesz.com/s/Zaa
Then, to narrow it down a bit further, I tried running the query on
another database. It ran much faster.
So I made a script that creates tables and foreign keys on a database,
to find out at which number of tables/foreign keys the query started to
slow down. I managed to get identically slow performance when I had 1000
tables and 5000 foreign keys. Which didn't help at all, since the
database in which the problem occurs has only 292 tables and 521 foreign
keys.
Of course, it is possible to change the code and use a (different)
manual query that does the same and runs perfectly fine, I've already
done that. But why does this happen, from 9.0.5 on? Is there any idea?
Is this situation already known?
I hope someone can enlighten me on this subject..
Thanks in advance! Best regards,
Miguel Silva
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: miguel.silva@tactis.pt
Subject: Re: Query performance - normal on 9.0.4, slow from 9.0.5 onwards
In-Reply-To: <4EFDE928.50807@tactis.pt>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox