so 13. 6. 2020 v 7:13 odesÃlatel Pavel Stehule <pavel.steh...@gmail.com> napsal:
> > > 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? > and maybe workaround. Can we use functional index there. It has a statistics. Pavel > Regards > > Pavel > > >> >> -- >> Justin >> >> >>
