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

Miguel Silva <miguel(dot)silva(at)tactis(dot)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=3505862a8d3e3b389ab926346061b7135fa44f79

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