On Fri, Dec 30, 2011 at 10:39 AM, Miguel Silva <miguel(dot)silva(at)tactis(dot)pt> wrote:
> 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:
> 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
> 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
> 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..
try this (curious):
create table pos as select n from generate_series(1,32) n;
and swap that for the in-query generate series call. your statistics
in the query are completely off (not 100% sure why), so I'm thinking
to replace that since it lies to the planner about the # rows
returned. also the join on the array element probably isn't helping.
In response to
pgsql-performance by date
|Next:||From: Tom Lane||Date: 2011-12-30 22:29:03|
|Subject: Re: Query performance - normal on 9.0.4, slow from 9.0.5 onwards |
|Previous:||From: Miguel Silva||Date: 2011-12-30 17:50:08|
|Subject: Re: Query performance - normal on 9.0.4, slow from 9.0.5