Thread: pgsql-server/doc TODO
CVSROOT: /cvsroot
Module name: pgsql-server
Changes by: momjian@svr1.postgresql.org 04/05/19 23:01:00
Modified files:
doc : TODO
Log message:
Add:
> * Allow col IS TRUE/FALSE use an index like col = TRUE/FALSE
momjian@svr1.postgresql.org (Bruce Momjian) writes:
> Add:
> > * Allow col IS TRUE/FALSE use an index like col = TRUE/FALSE
They don't have the same semantics.
regards, tom lane
Tom Lane wrote: > momjian@svr1.postgresql.org (Bruce Momjian) writes: > > Add: > > > * Allow col IS TRUE/FALSE use an index like col = TRUE/FALSE > > They don't have the same semantics. Oh, they don't? Nulls? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> momjian@svr1.postgresql.org (Bruce Momjian) writes:
>>> Add:
>>> * Allow col IS TRUE/FALSE use an index like col = TRUE/FALSE
>>
>> They don't have the same semantics.
> Oh, they don't? Nulls?
Right.
On second thought it might be possible to optimize this in a similar
fashion to the IN optimizations, viz only at top level of WHERE, so that
you can pretend NULL is the same as FALSE. But it needs some careful
thought.
A possibly more relevant issue is that indexes on boolean columns are
seldom of any value anyway, and so optimizing behavior for them seems
pretty far down the priority list. In my experience it's more useful to
create an index on another column(s) with the boolean condition as a
partial-index predicate. In this context you can spell the condition
however you like, it just has to be the same spelling in queries as in
the index definition...
regards, tom lane
OK, I removed the TODO item. It seem too weird. If others complain about this, we can document it better. I personally didn't know IS TRUE/FALSE even worked. --------------------------------------------------------------------------- Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Tom Lane wrote: > >> momjian@svr1.postgresql.org (Bruce Momjian) writes: > >>> Add: > >>> * Allow col IS TRUE/FALSE use an index like col = TRUE/FALSE > >> > >> They don't have the same semantics. > > > Oh, they don't? Nulls? > > Right. > > On second thought it might be possible to optimize this in a similar > fashion to the IN optimizations, viz only at top level of WHERE, so that > you can pretend NULL is the same as FALSE. But it needs some careful > thought. > > A possibly more relevant issue is that indexes on boolean columns are > seldom of any value anyway, and so optimizing behavior for them seems > pretty far down the priority list. In my experience it's more useful to > create an index on another column(s) with the boolean condition as a > partial-index predicate. In this context you can spell the condition > however you like, it just has to be the same spelling in queries as in > the index definition... > > regards, tom lane > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073