2

I have a PostgreSQL table with the following schema and indexes

+---------+---------+-----------+----------+--------------+-------------+
| Column  | Type    | Modifiers | Storage  | Stats target | Description |
|---------+---------+-----------+----------+--------------+-------------|
| number1 | integer |  not null | plain    | <null>       | <null>      |
| number2 | integer |  not null | plain    | <null>       | <null>      |
| account | bytea   |           | extended | <null>       | <null>      |
+---------+---------+-----------+----------+--------------+-------------+
Indexes:
    "test_pkey" PRIMARY KEY, btree (number1, number2)
    "test_account_idx" btree (account, number1, number2)
Has OIDs: no

When I query filtering WHERE "account" IS NULL

EXPLAIN
SELECT *
FROM "test"
WHERE "account" IS NULL
ORDER BY
  "number1" ASC,
  "number2" ASC
;

The query plan does not use the optimal index "test_account_idx"

+-------------------------------------------------------------------------------+
| QUERY PLAN                                                                    |
|-------------------------------------------------------------------------------|
| Index Scan using test_pkey on test  (cost=0.29..4672.97 rows=106319 width=29) |
|   Filter: (account IS NULL)                                                   |
+-------------------------------------------------------------------------------+

However, when I filter on WHERE "account" = <value> instead of IS NULL

EXPLAIN
SELECT *
FROM "test"
WHERE "account" = '\x00'
ORDER BY
  "number1" ASC,
  "number2" ASC
;

The query plan DOES use the optimal index

+-----------------------------------------------------------------------------------+
| QUERY PLAN                                                                        |
|-----------------------------------------------------------------------------------|
| Index Only Scan using test_account_idx on test  (cost=0.42..4.50 rows=5 width=29) |
|   Index Cond: (account = '\x00'::bytea)                                           |
+-----------------------------------------------------------------------------------+

Why does filtering on NULL instead of a value make the query so much less efficient?

1
  • Did you try a an additional filtered index: create index on test (number1, number2) where account is null?
    – user1822
    Commented Dec 15, 2022 at 10:26

2 Answers 2

3

You might find this thread enlightening, if it is not by you anyway. I'll quote Tom's answer:

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.

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.

1

You can work around this planner shortcoming by adding the NULLed column into the ORDER BY:

EXPLAIN
SELECT *
FROM "test"
WHERE "account" IS NULL
ORDER BY
  account,
  "number1" ASC,
  "number2" ASC;

It won't change the answer because all selected values will be tied on that column, but it does convince the planner to use the index.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.