PostgreSQL not using partial index when using boolean in WHERE clause

Question

I'm trying to create a partial index which includes rows based on a boolean value but in my tests neither rows with the value set to TRUE or FALSE are included in the index, or at least the index isn't being used.

Example schema

CREATE TABLE pitest
(
    id SERIAL,
    name TEXT,
    location TEXT,
    inactive BOOLEAN DEFAULT false NOT NULL
);

CREATE INDEX idx_name ON pitest (name) WHERE inactive IS NOT TRUE;

Example rows

id  |        name         |          location          | inactive 
----+---------------------+----------------------------+----------
101 | Anne Lopez          | Bajiao                     | t
102 | Jane Garcia         | Sanhe                      | f

I've populated the table with 1000 rows of which 109 have inactive = TRUE and the remaining 891 have inactive = FALSE.

Queries that should and shouldn't match the WHERE clause in the index both result in a sequential scan:

# EXPLAIN SELECT location FROM pitest WHERE name = 'Anne Lopez';
                      QUERY PLAN                       
-------------------------------------------------------
 Seq Scan on pitest  (cost=0.00..21.50 rows=1 width=9)
   Filter: (name = 'Anne Lopez'::text)

# EXPLAIN SELECT location FROM pitest WHERE name = 'Jane Garcia';
                      QUERY PLAN                       
-------------------------------------------------------
 Seq Scan on pitest  (cost=0.00..21.50 rows=1 width=9)
   Filter: (name = 'Jane Garcia'::text)

I've tried this on 2 different servers, 9.3 and 9.4 and with a larger test table with >1.5m rows of which around 150,000 had inactive = TRUE.

I'm trying to minimise the amount of disk space used by indexes by only indexing users without the 'inactive' flag set, but don't want to have to have to specify this flag in queries. Is it possible to use partial indexes in this way? Thanks!

Answer 1

Partial indexes make sense when they match the query pattern. In your case if you would have queries with inactive IS NOT TRUE condition then the index would be used otherwise it's simply not useful.

Cannot use the partial index:

SELECT location FROM pitest WHERE name = 'Anne Lopez';

Can benefit from the index:

SELECT location FROM pitest WHERE name = 'Anne Lopez' AND inactive IS NOT TRUE;