Re: Query performance - normal on 9.0.4, slow from 9.0.5 onwards

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:
>
> "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