Re: view reading information_schema is slow in PostgreSQL 12

2020-06-16 Thread regrog
I tested both postgres 12.3 and 13 beta 1 and the results are the same. I could read the pg_ tables instead of the views in the information_schema but that's the SQL standard schema so I'd prefer to stick to that. I reported this issue because the performance gap is huge and that could be useful

Re: view reading information_schema is slow in PostgreSQL 12

2020-06-15 Thread Michael Lewis
On Fri, Jun 12, 2020 at 12:26 PM 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. > > I have a view that abstracts the data in the database: > > CREAT

Re: view reading information_schema is slow in PostgreSQL 12

2020-06-13 Thread Tom Lane
David Rowley writes: > On Sat, 13 Jun 2020 at 19:52, David Rowley wrote: >> On Sat, 13 Jun 2020 at 16:07, Tom Lane wrote: >>> Hm. Maybe we could reasonably assume that the equality operators used >>> for such constructs are error-and-side-effect-free, thus dodging the >>> semantic problem I men

Re: view reading information_schema is slow in PostgreSQL 12

2020-06-13 Thread David Rowley
On Sat, 13 Jun 2020 at 19:52, David Rowley wrote: > > On Sat, 13 Jun 2020 at 16:07, Tom Lane wrote: > > > > David Rowley writes: > > > I wondered if it would be more simple to add some smarts to look a bit > > > deeper into case statements for selectivity estimation purposes. An > > > OpExpr lik

Re: view reading information_schema is slow in PostgreSQL 12

2020-06-13 Thread David Rowley
On Sat, 13 Jun 2020 at 16:07, Tom Lane wrote: > > David Rowley writes: > > I wondered if it would be more simple to add some smarts to look a bit > > deeper into case statements for selectivity estimation purposes. An > > OpExpr like: > > CASE c.contype WHEN 'c' THEN 'CHECK' WHEN 'f' THEN 'FOREIG

Re: view reading information_schema is slow in PostgreSQL 12

2020-06-12 Thread Pavel Stehule
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

2020-06-12 Thread Pavel Stehule
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

2020-06-12 Thread Pavel Stehule
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

2020-06-12 Thread Justin Pryzby
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

2020-06-12 Thread Tom Lane
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

2020-06-12 Thread David Rowley
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

2020-06-12 Thread David Rowley
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

2020-06-12 Thread Tom Lane
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

2020-06-12 Thread Imre Samu
> 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

view reading information_schema is slow in PostgreSQL 12

2020-06-12 Thread regrog
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