Re: Query performance - normal on 9.0.4, slow from 9.0.5 onwards
От | Miguel Silva |
---|---|
Тема | Re: Query performance - normal on 9.0.4, slow from 9.0.5 onwards |
Дата | |
Msg-id | 4F019BA4.202@tactis.pt обсуждение исходный текст |
Ответ на | Re: Query performance - normal on 9.0.4, slow from 9.0.5 onwards (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | 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
В списке pgsql-performance по дате отправления:
Предыдущее
От: Miguel SilvaДата:
Сообщение: Re: Query performance - normal on 9.0.4, slow from 9.0.5 onwards