Skip site navigation (1) Skip section navigation (2)

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

From: Miguel Silva <miguel(dot)silva(at)tactis(dot)pt>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Query performance - normal on 9.0.4, slow from 9.0.5 onwards
Date: 2012-01-02 11:57:24
Message-ID: 4F019BA4.202@tactis.pt (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: Strange, John WDate: 2012-01-03 18:23:44
Subject: Re: Slow nested loop execution on larger server
Previous:From: Miguel SilvaDate: 2012-01-02 11:44:43
Subject: Re: Query performance - normal on 9.0.4, slow from 9.0.5 onwards

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group