so 13. 6. 2020 v 6:34 odesÃlatel Justin Pryzby <pry...@telsasoft.com> napsal:
> On Fri, Jun 12, 2020 at 11:11:09PM -0400, Tom Lane wrote: > > regrog <andrea.venc...@gmail.com> writes: > > > I'm facing performance issues migrating from postgres 10 to 12 (also > from 11 > > > to 12) even with a new DB. > > > The simple query: select * from my_constraints is normal but as soon > as I > > > add where constraint_type = 'FOREIGN KEY' it takes a lot of time. > > > > I looked at this a bit. I see what's going on, but I don't see an easy > > workaround :-(. The information_schema.table_constraints view contains > > a UNION ALL, which in your v10 query produces this part of the plan: > > > To get a decent plan out of v12, the problem is to get it to produce > > a better rowcount estimate for the first arm of table_constraints' > > UNION. We don't necessarily need it to match the 1800 reality, but > > we need it to be more than 1. Unfortunately there's no simple way > > to affect that. The core misestimate is here: > > > I expect you're getting a fairly decent estimate for the "contype <> > > ALL" condition, but the planner has no idea what to make of the CASE > > construct, so it just falls back to a hard-wired default estimate. > > > > I don't have any good suggestions at the moment. If you had a lot more > > tables (hence more rows in pg_constraint) the plan would likely shift > > to something tolerable even with the crummy selectivity estimate for the > > CASE. But where you are, it's hard. A conceivable workaround is to > > drop the "tc.constraint_type <> 'CHECK'" condition from your view, which > > would resurrect that UNION arm and probably get you back to something > > similar to the v10 plan. > > For the purposes of making this work for v12, you might try to look at > either a > temporary table: > > CREATE TEMP TABLE constraints AS SELECT * FROM > information_schema.table_constraints WHERE constraint_type='FOREIGN KEY'; > ANALYZE constraints; > SELECT * FROM ... LEFT JOIN constraints LEFT JOIN ... > > or a CTE (which, if it works, is mostly dumb luck): > WITH constraints AS MATERIALIZED (SELECT * FROM > information_schema.table_constraints) SELECT * FROM constraints WHERE > constraint_type='FOREIGN KEY'; > > Or make a copy of the system view with hacks for the worst misestimates > (like > contype<>'c' instead of constraint_type<>'CHECK'). > Tomas Vondra is working on functional statistics. Can it be the solution of CASE issue? Regards Pavel > > -- > Justin > > >
