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.
Date: Sun, 19 Nov 2023 11:58:42 -0500
Message-ID: <1115210.1700413122@sss.pgh.pa.us> (raw)
In-Reply-To: <18206-5a796f4945e5b39f@postgresql.org>
References: <18206-5a796f4945e5b39f@postgresql.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, dmigowski@ikoffice.de, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18206: Strange performance behaviour depending on location of field in query.
In-Reply-To: <1115210.1700413122@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox