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
Last activity: Dec 15, 2022, 04:38 PM