From: Tom Lane <tgl@sss.pgh.pa.us>
To: dmigowski@ikoffice.de
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18206: Strange performance behaviour depending on location
 of field in query.
In-reply-to: <18206-5a796f4945e5b39f@postgresql.org>
References: <18206-5a796f4945e5b39f@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Sun, 19 Nov 2023 15:33:16 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1115209.1700413122.1@sss.pgh.pa.us>
Date: Sun, 19 Nov 2023 11:58:42 -0500
Message-ID: <1115210.1700413122@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1115210.1700413122%40sss.pgh.pa.us>
Precedence: bulk

PG Bug reporting form <noreply@postgresql.org> writes:
> I have a table with some columns id and a to z and these two queries have
> drastically different runtimes.

>   select count(id) FROM testtable t WHERE t.z IS NULL;  (35% slower than
> WHERE t.a IS NULL)
>   select count(id) FROM testtable t WHERE t.a IS NULL;

> This just screems like somewhere PostgreSQL is iterating over fields over
> and over again without assigning field indexes to the literals. 

This is entirely unsurprising.  There's no cheap way to extract values
from a row that contains nulls: the offset of the field you want can't
be determined without iterating over all the fields before it, since
some of them might not be there.

One could imagine ways to optimize queries of this exact form: if
the WHERE clause is "WHERE t.z IS [NOT] NULL" and nothing else,
in principle it could be checked by examining z's bit in the nulls
bitmap, without really extracting any field values.  But that'd
require adding some remarkably ugly warts to the clause evaluation
mechanism, and I doubt it would be worth the trouble.

			regards, tom lane