[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: [PERFORM] Performant queries on table with many boolean columns
From:       Adam Brusselback <adambrusselback () gmail ! com>
Date:       2016-04-25 17:23:28
Message-ID: CAMjNa7dqiG4xDB8yGK47zyeRnqf_yJSMbLBihOeqZOg+oVXH8g () mail ! gmail ! com
[Download RAW message or body]

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}'



[Attachment #3 (text/html)]

<div dir="ltr"><div class="gmail_extra">At that point would it be better to just use \
a boolean array?</div><div class="gmail_extra"><br></div><div \
class="gmail_extra">Here is an example I just wrote up that does pretty damn fast \
searches.</div><div class="gmail_extra"><br></div><div \
class="gmail_extra"><br></div><div class="gmail_extra"><blockquote style="margin:0px \
0px 0px 40px;border:none;padding:0px"><div class="gmail_extra"><div \
class="gmail_extra"><div class="gmail_extra">SET work_mem = &#39;256 \
MB&#39;;</div><div class="gmail_extra"><br></div><div class="gmail_extra">CREATE \
TABLE test_bool AS  </div><div class="gmail_extra">SELECT id, array_agg(random() &lt; \
0.85) as boolean_column</div><div class="gmail_extra">FROM generate_series(1, \
100)</div><div class="gmail_extra">CROSS JOIN generate_series(1, 500000) id</div><div \
class="gmail_extra">GROUP BY id;</div><div class="gmail_extra"><br></div><div \
class="gmail_extra">CREATE INDEX idx_test_bool ON test_bool \
(boolean_column);</div><div class="gmail_extra"><br></div><div \
class="gmail_extra">VACUUM ANALYZE test_bool;</div><div \
class="gmail_extra"><br></div><div class="gmail_extra">SELECT *</div><div \
class="gmail_extra">FROM test_bool</div><div class="gmail_extra">ORDER BY   \
random()</div><div class="gmail_extra">LIMIT 10</div><div \
class="gmail_extra"><br></div><div class="gmail_extra">SELECT id</div><div \
class="gmail_extra">FROM test_bool</div><div class="gmail_extra">WHERE boolean_column \
= &#39;{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}&#39;</div></div></div></blockquote></div><div \
class="gmail_extra"><br></div></div>



[prev in list] [next in list] [prev in thread] [next in thread]