DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=default; d=tactis.pt;
	h=Received:Message-ID:Disposition-Notification-To:Date:From:Reply-To:Organization:User-Agent:MIME-Version:To:CC:Subject:References:In-Reply-To:Content-Type:Content-Transfer-Encoding:X-Source:X-Source-Args:X-Source-Dir;
	b=N0MtGtj2lW03LAoACHh2+tePRlQUEXo6e9IolViFlX6mlj1FnzDjpBtqkkdZnMlyIk6o2r/9rQ417E0IFJ6o1eURqyAuX2F6BxLC5PZtvqivFAQyJHwDEahNF1mlyhPU;
Message-ID: <4F019BA4.202@tactis.pt>
Date: Mon, 02 Jan 2012 11:57:24 +0000
From: Miguel Silva <miguel.silva@tactis.pt>
Reply-To: miguel.silva@tactis.pt
Organization: TACTIS , LDA
User-Agent: Mozilla/5.0 (Windows NT 6.1;
 rv:8.0) Gecko/20111105 Thunderbird/8.0
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: pgsql-performance@postgresql.org
Subject: Re: Query performance - normal on 9.0.4, slow from 9.0.5
 onwards
References: <4EFDE928.50807@tactis.pt> <24198.1325284143@sss.pgh.pa.us>
In-Reply-To: <24198.1325284143@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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