Query performance - normal on 9.0.4, slow from 9.0.5 onwards - Mailing list pgsql-performance

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