Miguel Silva <miguel.silva@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
