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

Hello Andres,

Half of them are null in average, 30% are even not null. The test case I sent you was artificial to show the problem, but it's the same slower behaviour when I fill all the columns with values except the one in the where clause.

Greeting,
Daniel

-----Ursprüngliche Nachricht-----
Von: Andres Freund <andres(at)anarazel(dot)de>
Gesendet: Sonntag, 19. November 2023 23:18
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 21:15:37 +0000, Daniel Migowski wrote:
> Thank you for the detailed explanation. It surely has a reason to
> remove the previously used short-circuit in slot_getattr, at least in
> the special case when we

The short-circuit in slot_getattr() still exists, we just don't reach it in this case.

> 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?

> Don't know how to handle this, maybe I should rework the layout of all
> my tables and move the mostly used columns to the beginning.

It might also be worth to split the table into multiple tables and join when necessary. Independent of this issue, relatively wide tuples, where you don't use most of the columns, will cause unnecessary IO.

Greetings,

Andres