Re: Performant queries on table with many boolean columns - Mailing list pgsql-performance

From Adam Brusselback
Subject Re: Performant queries on table with many boolean columns
Date
Msg-id CAMjNa7dqiG4xDB8yGK47zyeRnqf_yJSMbLBihOeqZOg+oVXH8g@mail.gmail.com
Whole thread Raw
In response to Re: Performant queries on table with many boolean columns  (Merlin Moncure <mmoncure@gmail.com>)
List pgsql-performance
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}'

pgsql-performance by date:

Previous
From: Merlin Moncure
Date:
Subject: Re: Performant queries on table with many boolean columns
Next
From: "Sven Kerkling"
Date:
Subject: Re: Performance problems with postgres and null Values?