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!