Re: Planner question - "bit" data types

Поиск
Список
Период
Сортировка
От Karl Denninger
Тема Re: Planner question - "bit" data types
Дата
Msg-id 4AA2D390.9070702@denninger.net
обсуждение исходный текст
Ответ на Re: Planner question - "bit" data types  (Tom Lane <tgl@sss.pgh.pa.us>)
Ответы Re: Planner question - "bit" data types  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-performance
Tom Lane wrote:
Merlin Moncure <mmoncure@gmail.com> writes: 
If you are only interested in one or a very small number of cases of
'permission', you can use an expression index to target constant
values:   
 
"select ... from .... where ...... and (permission & mask = permission)"   
 
create index foo_permission_xyz_idx on foo((64 & mask = 64));
select * from foo where 64 & mask = 64; --indexed!   
A possibly more useful variant is to treat the permission condition
as a partial index's WHERE condition.  The advantage of that is that
the index's actual content can be some other column, so that you can
combine the permission check with a second indexable test.  The index
is still available for queries that don't use the other column, but
it's more useful for those that do.
		regards, tom lane
 
That doesn't help in this case as the returned set will typically be quite large, with the condition typically being valid on anywhere from 10-80% of the returned tuples.

What I am trying to avoid is creating a boolean column for EACH potential bit (and an index on each), as that makes the schema non-portable for others and quite messy as well - while there are a handful of "known masks" the system also has a number of "user defined" bit positions that vary from installation to installation.


-- Karl
Вложения

В списке pgsql-performance по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Planner question - "bit" data types
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Planner question - "bit" data types