booleans_benchmark.sql

-- $> createdb boolean_test
-- $> psql boolean_test

CREATE TABLE benchmark_booleans (
  id SERIAL UNIQUE NOT NULL,
  is_deleted boolean, -- not unique
  deleted_at timestamp
);

-- Using B-Tree default
CREATE INDEX idx_is_deleted on benchmark_booleans (is_deleted);
CREATE INDEX idx_deleted_at on benchmark_booleans (deleted_at);

INSERT INTO benchmark_booleans (is_deleted, deleted_at)
SELECT 
 (RANDOM() > 0.5) as is_deleted,
 case when RANDOM() < 0.5 
     then null
     else now() - trunc(random() * 20) * '1 year'::interval
      + trunc(random() * 365) * '1 day'::interval 
 end as deleted_at
 FROM generate_series(1,10000000);
-- Takes a couple minutes

\timing

select count(*) from benchmark_booleans where is_deleted = true;
Time: 1355.556 ms
Time: 1041.068 ms
Time: 1054.470 ms

select count(*) from benchmark_booleans where deleted_at is null;
Time: 996.164 ms
Time: 990.963 ms
Time: 1015.236 ms


Index Stats @ 10M rows

tablename          |         indexname         | num_rows | table_size | index_size |
-------------------+---------------------------+----------+------------+------------+
benchmark_booleans | benchmark_booleans_id_key |        0 | 383 MB     | 214 MB     |
benchmark_booleans | idx_deleted_at            |        0 | 383 MB     | 277 MB     |
benchmark_booleans | idx_is_deleted            |        0 | 383 MB     | 272 MB     |