From: Tom Lane <tgl@sss.pgh.pa.us>
To: miguel.silva@tactis.pt
Cc: pgsql-performance@postgresql.org
Subject: Re: Query performance - normal on 9.0.4, slow from 9.0.5 onwards
Date: Fri, 30 Dec 2011 17:29:03 -0500
Message-ID: <24198.1325284143@sss.pgh.pa.us> (raw)
In-Reply-To: <4EFDE928.50807@tactis.pt>
References: <4EFDE928.50807@tactis.pt>
Miguel Silva <miguel.silva@tactis.pt> writes:
> 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.
I poked at this a little bit. AFAICS the only potentially relevant
planner change between 9.0.4 and 9.0.5 was the removal of eqjoinsel's
ndistinct-clamping heuristic,
http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=3505862a8d3e3b389ab9263460...
Now that's something we took out because it seemed to be making more
cases worse than better, but there were cases where it helped (for the
wrong reasons, but nonetheless it sometimes adjusted the estimates to be
closer to reality), and apparently you've got one such case. However,
removing that logic just brought the behavior back to what it was
pre-8.4, so I'm a bit dubious of the claim that this query has worked
well for "over 7 years". Perhaps you had lots fewer tables and/or FKs
back in pre-8.4 days?
I experimented with a toy database having 1000 tables of 30 columns
each, with one foreign key per table, all in the "public" schema, and
indeed this query is pretty slow on current releases. A big part of the
problem is that the planner is unaware that the one row you're selecting
from pg_namespace will join to almost all the rows in pg_class; so it
underestimates the sizes of those join results, and that leads to
picking a nestloop plan style where it's not appropriate.
I tried removing these WHERE conditions:
> AND pkn.nspname = 'public'
> AND fkn.nspname = 'public'
and got a decently fast plan. If those are, as I suspect, also no-ops
in your real database, perhaps that will do as a workaround.
regards, tom lane
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: tgl@sss.pgh.pa.us, miguel.silva@tactis.pt
Subject: Re: Query performance - normal on 9.0.4, slow from 9.0.5 onwards
In-Reply-To: <24198.1325284143@sss.pgh.pa.us>
* 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