AW: BUG #18205: Performance regression with NOT NULL checks.
От | Daniel Migowski |
---|---|
Тема | AW: BUG #18205: Performance regression with NOT NULL checks. |
Дата | |
Msg-id | 41ED3F5450C90F4D8381BC4D8DF6BBDC017401637E@EXCHANGESERVER.ikoffice.de обсуждение исходный текст |
Ответ на | Re: BUG #18205: Performance regression with NOT NULL checks. (Andres Freund <andres@anarazel.de>) |
Список | pgsql-bugs |
Hello Andreas, in this case the NOT NULL check matches all columns, but the other where clauses only match 30%. The software is used bymore than hundred customers and they all use different features, but my queries must of course work in every case. I have indeed a lot of tables with 80 columns or so, and will now try to restructure them so the important columns come sooner.But in many queries we do select maybe 5-20 fields and filter by an arbitrary field, so sparse selection of attributeswould be VERY beneficial for us in many of the expensive queries. Queries returning many rows or joining and aggregatingdata are mostly just returning part of the table, and that is for us where the performance is needed. Kind regards, Daniel Migowski PS: Btw many queries that result in showing a list of stuff in the GUI, like, invoice list with customer, show the name andnumber of customer from the customer table. We will now ensure that these "Display name" attributes will be at the startof the table so they can be retrieved efficiently. Another example for sparse column selection, but with the right organisationwe don't need to optimize here. -----Ursprüngliche Nachricht----- Von: Andres Freund <andres@anarazel.de> Gesendet: Sonntag, 19. November 2023 23:39 An: Daniel Migowski <dmigowski@ikoffice.de> Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org Betreff: Re: BUG #18205: Performance regression with NOT NULL checks. Hi, On 2023-11-19 14:17:44 -0800, Andres Freund wrote: > On 2023-11-19 21:15:37 +0000, Daniel Migowski wrote: > > And yes, in my real world use case which I tried to understand I > > have a wide table with 81 columns, and I am using column 1,43,18,75 > > and filter by attribute 82,42, and 24. > > Are most of the columns NULL or not? Another question: In the real query, how selective is the WHERE clause? In your test query all rows are returned and youhave no columns in the select list, but it doesn't sound like that's quite your real workload... Greetings, Andres Freund
В списке pgsql-bugs по дате отправления: