Re: Performant queries on table with many boolean columns

[Adam Brusselback <adambrusselback@xxxxxxxxx>]

At that point would it be better to just use a boolean array?

Here is an example I just wrote up that does pretty damn fast searches.

SET work_mem = '256 MB';

CREATE TABLE test_bool AS 

SELECT id, array_agg(random() < 0.85) as boolean_column

FROM generate_series(1, 100)

CROSS JOIN generate_series(1, 500000) id

GROUP BY id;

CREATE INDEX idx_test_bool ON test_bool (boolean_column);

VACUUM ANALYZE test_bool;

SELECT *

FROM test_bool

ORDER BY  random()

LIMIT 10

SELECT id

FROM test_bool

WHERE boolean_column = '{t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,f,t,t,t,t,f,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,f,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,f,f,t,t,t,t,t,t,t,t,t,f,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,f}'