Re: view reading information_schema is slow in PostgreSQL 12

so 13. 6. 2020 v 7:15 odesílatel Pavel Stehule napsal: > > > so 13. 6. 2020 v 7:13 odesílatel Pavel Stehule > napsal: > >> >> >> so 13. 6. 2020 v 6:34 odesílatel Justin Pryzby >> napsal: >> >>> On Fri, Jun 12, 2020 at 11:11:09PM -0400, Tom Lane wrote: >>> > regrog writes: >>> > > I'm facing pe

Re: view reading information_schema is slow in PostgreSQL 12

so 13. 6. 2020 v 7:13 odesílatel Pavel Stehule napsal: > > > so 13. 6. 2020 v 6:34 odesílatel Justin Pryzby > napsal: > >> On Fri, Jun 12, 2020 at 11:11:09PM -0400, Tom Lane wrote: >> > regrog writes: >> > > I'm facing performance issues migrating from postgres 10 to 12 (also >> from 11 >> > >

Re: view reading information_schema is slow in PostgreSQL 12

so 13. 6. 2020 v 6:34 odesílatel Justin Pryzby napsal: > On Fri, Jun 12, 2020 at 11:11:09PM -0400, Tom Lane wrote: > > regrog 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_con

Re: view reading information_schema is slow in PostgreSQL 12

On Fri, Jun 12, 2020 at 11:11:09PM -0400, Tom Lane wrote: > regrog 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 = '

Re: view reading information_schema is slow in PostgreSQL 12

David Rowley writes: > On Sat, 13 Jun 2020 at 15:11, Tom Lane wrote: >> 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. > This feels

Re: view reading information_schema is slow in PostgreSQL 12

On Sat, 13 Jun 2020 at 15:11, Tom Lane wrote: > 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. This feels quite similar to [1]. I wond

Re: view reading information_schema is slow in PostgreSQL 12

On Sat, 13 Jun 2020 at 06:26, regrog wrote: > > I'm facing performance issues migrating from postgres 10 to 12 (also from 11 > to 12) even with a new DB. > Th performance difference is huge 300ms in pg10 vs 3 minutes in pg12. This appears to be down to bad statistics that cause pg12 to choose a n

Re: view reading information_schema is slow in PostgreSQL 12

regrog 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

Re: view reading information_schema is slow in PostgreSQL 12

> view reading information_schema is slow in PostgreSQL 12 Hi, What is the PG version? IF PG < 12.3 THEN maybe related to this ? https://www.postgresql.org/docs/release/12.3/ ( Repair performance regression in information_schema.triggers view ) Imre regrog ezt írta (időpont: 2020. jún. 12.,

view reading information_schema is slow in PostgreSQL 12

I'm facing performance issues migrating from postgres 10 to 12 (also from 11 to 12) even with a new DB. Th performance difference is huge 300ms in pg10 vs 3 minutes in pg12. I have a view that abstracts the data in the database: CREATE OR REPLACE VIEW public.my_constraints AS SELECT lower(tc.cons