On Fri, Dec 30, 2011 at 10:39 AM, Miguel Silva <miguel.silva@xxxxxxxxx> 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: > > "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.. 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. merlin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
