Re: Performant queries on table with many boolean columns

От: Teodor Sigaev
Тема: Re: Performant queries on table with many boolean columns
Дата: ,
Msg-id: 5718A59D.4090706@sigaev.ru
(см: обсуждение, исходный текст)
Ответ на: Re: Performant queries on table with many boolean columns  ("David G. Johnston")
Ответы: Re: Performant queries on table with many boolean columns  ("David G. Johnston")
Список: pgsql-performance

Скрыть дерево обсуждения

Performant queries on table with many boolean columns  (Rob Imig, )
 Re: Performant queries on table with many boolean columns  (Teodor Sigaev, )
  Re: Performant queries on table with many boolean columns  (Rick Otten, )
  Re: Performant queries on table with many boolean columns  ("David G. Johnston", )
   Re: Performant queries on table with many boolean columns  (Teodor Sigaev, )
    Re: Performant queries on table with many boolean columns  ("David G. Johnston", )
  Re: Performant queries on table with many boolean columns  (Jeff Janes, )
   Re: Performant queries on table with many boolean columns  (Rob Imig, )
    Re: Performant queries on table with many boolean columns  (Rob Imig, )
     Re: Performant queries on table with many boolean columns  (bricklen, )
      Re: Performant queries on table with many boolean columns  (bricklen, )
       Re: Performant queries on table with many boolean columns  (Merlin Moncure, )
        Re: Performant queries on table with many boolean columns  (Adam Brusselback, )
 Re: Performant queries on table with many boolean columns  (Jeff Janes, )

>     looks like a deal for contrib/bloom index in upcoming 9.6 release
> ​Curious, it doesn't look like it will work with booleans out of the box.
> http://www.postgresql.org/docs/devel/static/bloom.html

There is no rocket science here:
# create table x (v bool);
# create index i on x using bloom ((v::int4));
# set enable_seqscan=off; --because of empty table
# explain select * from x where v::int4 = 1;
                             QUERY PLAN
------------------------------------------------------------------
  Bitmap Heap Scan on x  (cost=25.08..35.67 rows=14 width=1)
    Recheck Cond: ((v)::integer = 1)
    ->  Bitmap Index Scan on i  (cost=0.00..25.07 rows=14 width=0)
          Index Cond: ((v)::integer = 1)

Or cast it to "char" type (with quoting!)

--
Teodor Sigaev                                   E-mail: 
                                                    WWW: http://www.sigaev.ru/



В списке pgsql-performance по дате сообщения:

От: Teodor Sigaev
Дата:
Сообщение: Re: Performant queries on table with many boolean columns
От: "David G. Johnston"
Дата:
Сообщение: Re: Performant queries on table with many boolean columns