From: Tom Lane <tgl@sss.pgh.pa.us>
To: Steve Hill <steve@opendium.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Odd behaviour with indexes for NULLs
Date: Tue, 13 Dec 2022 10:34:39 -0500
Message-ID: <2333865.1670945679@sss.pgh.pa.us> (raw)
In-Reply-To: <5e4fa822-171a-5904-46e0-28a45ff66592@opendium.com>
References: <5e4fa822-171a-5904-46e0-28a45ff66592@opendium.com>
Steve Hill <steve@opendium.com> writes:
> In this case, rather than using the index to order the records, it uses
> a separate sorting step, which is considerably slower. The only
> difference between the two queries is that the one that doesn't use the
> index for sorting is looking for a NULL realm column, instead of a realm
> column which contains some text.
You're out of luck on that. It's true that "x IS NULL" can be matched
to a btree index, but that's a special-case hack that is not tied into
the logic that matches equivalence-class operators to ORDER BY.
> However, this other query does
> not work as I would expect:
> EXPLAIN ANALYZE SELECT * FROM sessions WHERE realm IS NOT NULL AND eui64
> = '9e:cc:b9:ff:fe:5d:28:0a' ORDER BY update_time DESC LIMIT 1;
Again, IS NOT NULL is a special case that isn't tied into as many
places as you might wish.
Generally speaking, using NULL as if it were a real, searchable value
is a bad idea that is going to lead you to grief. There are semantic
gotchas with that, inherent to SQL not just PG's fault, because of
the way they've overloaded NULL to mean a few different things.
Because of that, we've also not worked that hard on making the planner
super-intelligent about IS [NOT] NULL. I'm not even 100% sure that
we *could* safely optimize IS NULL in the same way as a normal
equivalence-class operator is handled; but even if it's semantically
sound, it hasn't gotten done.
You might think about reserving some value such as "*UNASSIGNED*"
for your realm column, and using that rather than NULL. Grotty,
I know, but less likely to have unforeseen gotchas.
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-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, steve@opendium.com, pgsql-admin@lists.postgresql.org
Subject: Re: Odd behaviour with indexes for NULLs
In-Reply-To: <2333865.1670945679@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