Re: Planner question - "bit" data types

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Merlin Moncure <mmoncure(at)gmail(dot)com>
Cc: Josh Berkus <josh(at)agliodbs(dot)com>, Karl Denninger <karl(at)denninger(dot)net>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Planner question - "bit" data types
Date: 2009-09-05 20:59:27
Message-ID: 7813.1252184367@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Merlin Moncure <mmoncure(at)gmail(dot)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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Karl Denninger 2009-09-05 21:09:36 Re: Planner question - "bit" data types
Previous Message Merlin Moncure 2009-09-05 20:09:07 Re: Planner question - "bit" data types