Thread: minor bug...

create table foo(b boolean);
create index foo_index on foo(b);

You get a "no default operator for type 16." error...

This ecommerce datamodel I'm porting over uses such indices 
frequently, apparently to grab small subsets of large tables which
have few rows with the predicate set to one state.  Even if such
an index might be of dubious usefulness in situations where
the table's population is more evenly split, there's no real
reason not to support indexes on booleans, is there?



- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.
 

Don Baccus <dhogaza@pacifier.com> writes:
> ... there's no real reason not to support indexes on booleans, is
> there?

Not that I can see.  Care to whip up the index support?  I think the
only actual new code needed is a three-way-compare function (return -1,
0, or +1 according as a < b, a = b, a > b).  Then you need to make up
the appropriate rows in pg_amop and related tables.  See the "xindex"
chapter of the documentation.

(It occurs to me that performance would probably suck, however, because
btree doesn't handle lots of equal keys very efficiently.  Fixing that
is on the TODO list...)
        regards, tom lane

> > ... there's no real reason not to support indexes on booleans, is
> > there?

afaict the only case where this would be a win is if there is a *very*
skewed distribution of boolean values, and you *only* want the
uncommon one. Otherwise, looking up half the rows in a table via index
has got to be worse than just scanning the table.

> Not that I can see.  Care to whip up the index support?  I think the
> only actual new code needed is a three-way-compare function (return -1,
> 0, or +1 according as a < b, a = b, a > b).  Then you need to make up
> the appropriate rows in pg_amop and related tables.  See the "xindex"
> chapter of the documentation.
> (It occurs to me that performance would probably suck, however, because
> btree doesn't handle lots of equal keys very efficiently.  Fixing that
> is on the TODO list...)

... And performance will suck anyway (see above) :)
               - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

Thomas Lockhart wrote:
> 
> > > ... there's no real reason not to support indexes on booleans, is
> > > there?
> 
> afaict the only case where this would be a win is if there is a *very*
> skewed distribution of boolean values, and you *only* want the
> uncommon one. Otherwise, looking up half the rows in a table via index
> has got to be worse than just scanning the table.

One (maybe only) case I can see use for it is for a multi-field index 
containing many booleans (say an index over 16 boolean fields).

------------
Hannu

I've submitted a patch to pgsql-patches to fix the following
limitations on type bool:

test=> create table  foo(b bool);
CREATE
test=> create index foo_idx on foo(b);
ERROR:  Can't find a default operator class for type 16.
test=> select * from foo where b<=b;
ERROR:  Unable to identify an operator '<=' for types 'bool' and 'bool'       You will have to retype this query using
anexplicit cast
 
test=> select * from foo where b>=b;
ERROR:  Unable to identify an operator '>=' for types 'bool' and 'bool'       You will have to retype this query using
anexplicit cast
 
test=> 

The oversite that leads to one not being able to define an index on
type bool I can understand, but who the heck would bother to go to
all the trouble of adding type "bool" and only define four of the
six standard comparison operators?

Oh well...

Tom suggested I submit the patch to pgsql-patches, and I ran my OID
assignments for the new procs, bool_ops, etc past Thomas at Tom's
suggestion, the regression tests pass, I've done some additional testing,
etc.

I didn't look into adding bool to the hash ops defined in pg_amop,
after all yesterday afternoon was the first I'd looked into adding
something to the catalog code and the getting the above set of
functions in took me four hours of reading docs and code, testing
making the diff, etc.

I assume not having a type added to hash ops isn't fatal, because
"numeric" isn't there and Jan strikes me as being a very thorough
guy...



- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.
 

> I assume not having a type added to hash ops isn't fatal, because
> "numeric" isn't there and Jan strikes me as being a very thorough
> guy...

A hash index is probably even less useful than the btree index for
this type, unless it can be used with multi-column indices. Because
the hash will chain duplicate values into a list of some kind, and
you'll get *long* lists.

Find and steal the code for "char" (the real one-byte character type).
But a one-bit hash is what you really want, so it may be better to
implement your own.
                 - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California