Thread: pgsql-server/doc TODO

pgsql-server/doc TODO

From
momjian@svr1.postgresql.org (Bruce Momjian)
Date:
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


Re: pgsql-server/doc TODO

From
Tom Lane
Date:
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

Re: pgsql-server/doc TODO

From
Bruce Momjian
Date:
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

Re: pgsql-server/doc TODO

From
Tom Lane
Date:
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

Re: pgsql-server/doc TODO

From
Bruce Momjian
Date:
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