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