PostgreSQL planner doesn't want to use bloom index for boolean columns

Question

I created a bloom index, and it turns out that PostgreSQL's query planner just doesn't want to use the index for boolean.

Here is my test setup:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS bloom;

-- Adapt bloom index to boolean type
DROP OPERATOR CLASS IF EXISTS bloom_boolean_ops USING bloom;
CREATE OPERATOR CLASS bloom_boolean_ops
DEFAULT FOR TYPE boolean USING bloom AS
OPERATOR  1  = (boolean, boolean),
FUNCTION  1  hashchar;

-- Do the same, but for uuid type
-- This is needed to just demonstrate that this way of defining custom operator classes
-- works for other types, but doesn't work for boolean.
DROP OPERATOR CLASS IF EXISTS bloom_uuid_ops USING bloom;
CREATE OPERATOR CLASS bloom_uuid_ops
DEFAULT FOR TYPE uuid USING bloom AS
OPERATOR  1  = (uuid, uuid),
FUNCTION  1  uuid_hash;

CREATE TABLE test_tbl (
    id uuid NOT NULL,
    flag boolean NOT NULL
);

INSERT INTO test_tbl (id, flag) VALUES
('e5526344-669c-46ba-aece-8fbc82007161', true),
('b25aefdf-2aba-4e5a-83c3-ec2da3b934ac', false);

CREATE INDEX test_bloom_idx ON test_tbl USING bloom (flag, id);

SET ENABLE_SEQSCAN = OFF;

-- Try the boolean column. For some reason, the query planner ignores the index.
EXPLAIN SELECT 1 FROM test_tbl WHERE flag = TRUE;
 -- Seq Scan on test_tbl  (cost=10000000000.00..10000000001.02 rows=1 width=4)
 --   Filter: flag

-- Try the uuid column. Now it works fine.
EXPLAIN SELECT 1 FROM test_tbl WHERE id = 'e5526344-669c-46ba-aece-8fbc82007161';
-- Bitmap Heap Scan on test_tbl  (cost=8.02..12.03 rows=1 width=4)
--   Recheck Cond: (id = 'e5526344-669c-46ba-aece-8fbc82007161'::uuid)
--   ->  Bitmap Index Scan on test_bloom_idx  (cost=0.00..8.02 rows=1 width=0)
--         Index Cond: (id = 'e5526344-669c-46ba-aece-8fbc82007161'::uuid)

So as illustrated above, the query planner chooses to use index for searching uuid, but it just ignores it if the column happens to be boolean.

Also boolean expression indexes (like CREATE INDEX ... ((column = value));) are broken in the same way.

I've already tried:

Split a multi-column index to individual single-column indexes (although it doesn't make make a lot of sense with bloom).
Cast boolean to integer. It works, but queries are not friendly to the ORM that I use. So this is not an option for me.
Use different hash functions: hashint2, hashint4, hashchar("char") for the operator class. No effect.

Any suggestions?

(and yeah, I'm aware of partial indexes, they are not quite what I want)

user_6464308 · Answer

The reason is that the optimizer changes the query condition for a boolean like this:

 Seq Scan on test_tbl
   Filter: flag

Since the condition is no longer of the form expression = constant, the bloom index isn't used.

You can force the optimizer to retain the original shape of the condition by using a subquery:

EXPLAIN (COSTS OFF) SELECT 1 FROM test_tbl WHERE flag = (SELECT TRUE);

                QUERY PLAN                 
-------------------------------------------
 Bitmap Heap Scan on test_tbl
   Recheck Cond: (flag = $0)
   InitPlan 1 (returns $0)
     ->  Result
   ->  Bitmap Index Scan on test_bloom_idx
         Index Cond: (flag = $0)
(6 rows)

But I guess that your Obfuscating Rewrite Mapper won't be happy to generate such queries either.

One could consider an implicit type cast from boolean to integer, but you cannot do that since there already is a (non-implicit) such type cast, so you'd have to delete that first. Messing with the catalogs in that fashion is not what I would recommend.

PostgreSQL planner doesn't want to use bloom index for boolean columns

1 Answers