AW: BUG #18205: Performance regression with NOT NULL checks.

Hello Andreas,

in this case the NOT NULL check matches all columns, but the other where clauses only match 30%. The software is used by more 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 attributes would be VERY beneficial for us in many of the expensive queries. Queries returning many rows or joining and aggregating data 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 and number of customer from the customer table. We will now ensure that these "Display name" attributes will be at the start of the table so they can be retrieved efficiently. Another example for sparse column selection, but with the right organisation we don't need to optimize here.

-----Ursprüngliche Nachricht-----
Von: Andres Freund <andres(at)anarazel(dot)de>
Gesendet: Sonntag, 19. November 2023 23:39
An: Daniel Migowski <dmigowski(at)ikoffice(dot)de>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>; pgsql-bugs(at)lists(dot)postgresql(dot)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 you have no columns in the select list, but it doesn't sound like that's quite your real workload...

Greetings,

Andres Freund