3

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!

  • 2
    How could the db engine guess whether you're asking for the part of the data that is indexed or not? On top of that, you don't have a unique constraint on the name, so it has to search the whole table no matter what - looking for active ones via the index and inactive ones via a full search is a waste of time. – Mat Mar 7 '16 at 16:07
  • Both good points - I hadn't considered them. Thanks for your reply. – AlexH Mar 7 '16 at 16:27
  • The question title has "when using boolean in WHERE clause" but I see no boolean in WHERE clause ... – ypercubeᵀᴹ Mar 8 '16 at 16:14
2

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.