Sample Header Ad - 728x90

PostgreSQL WHERE IS NULL causes bad query plan

2 votes
2 answers
1369 views
I have a PostgreSQL table with the following schema and indexes
+---------+---------+-----------+----------+--------------+-------------+
| Column  | Type    | Modifiers | Storage  | Stats target | Description |
|---------+---------+-----------+----------+--------------+-------------|
| number1 | integer |  not null | plain    |        |       |
| number2 | integer |  not null | plain    |        |       |
| account | bytea   |           | extended |        |       |
+---------+---------+-----------+----------+--------------+-------------+
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" = 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?
Asked by nick314 (157 rep)
Dec 15, 2022, 03:59 AM
Last activity: Dec 15, 2022, 04:38 PM