From: Miguel Silva <miguel.silva@tactis.pt>
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
Date: Mon, 02 Jan 2012 11:57:24 +0000
Message-ID: <4F019BA4.202@tactis.pt> (raw)
In-Reply-To: <24198.1325284143@sss.pgh.pa.us>
References: <4EFDE928.50807@tactis.pt>
<24198.1325284143@sss.pgh.pa.us>
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=3505862a8d3e3b389ab9263460...
>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: miguel.silva@tactis.pt, tgl@sss.pgh.pa.us
Subject: Re: Query performance - normal on 9.0.4, slow from 9.0.5 onwards
In-Reply-To: <4F019BA4.202@tactis.pt>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox