On 30-12-2011 22:29, Tom Lane wrote:
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?
Well, thanks, that clarifies the reason why this happens!
Perhaps you are right. I mean, that's what I've been told, and I believe
it really worked well for all that time. But since this is an
auto-generated query, maybe it hasn't always been exactly like this. Or
maybe there really were fewer tables/FKs, back then.
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
I tried running the query with that change, but it still takes around 25
secs. What I did as a workaround, was use this query instead of an
auto-generated one:
SELECT
tc.constraint_name AS FK_NAME,
tc.table_name AS PKTABLE_NAME,
kcu.column_name AS PKCOLUMN_NAME,
ccu.table_name AS FKTABLE_NAME,
ccu.column_name AS FKCOLUMN_NAME,
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
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu ON
tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu ON
ccu.constraint_name = tc.constraint_name
JOIN pg_catalog.pg_constraint AS con ON con.conname =
tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY';
Thanks for looking into this!
Best regards,
Miguel Silva
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
