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

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