PostgreSQL partial index not used depending on boolean comparison

Question

I need help understanding why a partial index would be used in one query and would not in second one which is effectively the same.

I have the following table:

     Column     |           Type           | Collation | Nullable |   Default
----------------+--------------------------+-----------+----------+-------------
 id             | text                     |           | not null |
 field1         | text                     |           | not null |
 field2         | text                     |           | not null |
 field3         | boolean                  |           | not null |  false

With a partial index:

CREATE INDEX idx__my_table_index
    ON my_table(field1, field2)
    WHERE field3 IS FALSE;

and the following two queries:

postgres=> explain select distinct field1, field2 from my_table where not field3;
-[ RECORD 1 ]----------------------------------------------------------------------------
QUERY PLAN | Unique  (cost=113849.28..113849.66 rows=36 width=29)
-[ RECORD 2 ]----------------------------------------------------------------------------
QUERY PLAN |   ->  Sort  (cost=113849.28..113849.37 rows=38 width=29)
-[ RECORD 3 ]----------------------------------------------------------------------------
QUERY PLAN |         Sort Key: field1, field2
-[ RECORD 4 ]----------------------------------------------------------------------------
QUERY PLAN |         ->  Seq Scan on my_table  (cost=0.00..113848.28 rows=38 width=29)
-[ RECORD 5 ]----------------------------------------------------------------------------
QUERY PLAN |               Filter: (NOT field3)

and the second:

postgres=> explain select distinct field1, field2 from my_table where field3 IS FALSE;
-[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Unique  (cost=0.14..13.03 rows=36 width=29)
-[ RECORD 2 ]-----------------------------------------------------------------------------------------------------------------------
QUERY PLAN |   ->  Index Only Scan using idx__my_table_index on reservation  (cost=0.14..12.75 rows=38 width=29)

P.S. I'm using PostgreSQL 9.6

What happens if you create the index with WHERE field3 = FALSE? — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented May 31, 2019 at 17:02

Erwin Brandstetter · Accepted Answer · 2019-05-31 21:03:38Z

The exact laws of the game can be found in the manual, chapter Partial Indexes:

To be precise, a partial index can be used in a query only if the system can recognize that the WHERE condition of the query mathematically implies the predicate of the index. PostgreSQL does not have a sophisticated theorem prover that can recognize mathematically equivalent expressions that are written in different forms. (Not only is such a general theorem prover extremely difficult to create, it would probably be too slow to be of any real use.) The system can recognize simple inequality implications, for example “x < 1” implies “x < 2”; otherwise the predicate condition must exactly match part of the query's WHERE condition or the index will not be recognized as usable.

Bold emphasis mine.

Accordingly, the query predicate where not field3 is not recognized as matching the index predicate where field3 IS FALSE, so the query planner does not consider it.

mustaccio · Accepted Answer · 2019-05-31 16:40:43Z

These queries may have slightly different semantics, from the query parser point of view.

select ... where field3 IS FALSE means "evaluate expression field3 IS FALSE and return rows for which the evaluation result is TRUE". The expression matches that of your partial index, so it's a natural choice.

select ... where not field3 means "evaluate expression NOT field3 and return rows for which the evaluation result is TRUE". Here the expression does not match the partial index.

We know that these expressions are logically equivalent, particularly in the presence of the NOT NULL constraint on the column in question, but the parser (or the optimizer) apparently cannot see that.

I suspect if you change your partial index condition to WHERE NOT field3 you'll see that the plans for your two queries switch places.

Stack Exchange Network

PostgreSQL partial index not used depending on boolean comparison

2 Answers 2

Your Answer

Hot Network Questions

PostgreSQL partial index not used depending on boolean comparison

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions