On Fri, 15 Mar 2002, Dmitry Tkach wrote:
...
> Yes, and know... (Yes, it will give th ecorrect answer, and NO it will
> not use the index).
> The thing is, that it will only use a (btree) index for one of the
> 'comparison' operators
> (<=, <, =, >, >=), that compare the value in the table with the query
> parameter - so,
> bitstring = 7, or bitstring < 7 etc... will work, but
> 'do_something_to_bitstring = 7' will not (I mean, it will, but it won't
> use the index).
Okay, I'm recovering from my encounter with the stupid stick.
I understand this:
1. You want to use a btree index because presumably it's faster than a
normal index.
2. A btree index is a binary tree index that uses the order of values to
find an answer quickly.
3. In your case for instance: a value of 10 should produce a resultset
with bitstrings of 10, 11, 14, 26, ...
So yeah, since 12 and 13 are > 10, normal equality operators won't work.
If a solution were to be found, I think it should base the ordering on
the bits from least significant bit to most significant. So, for 1st bit
go left in the tree for a 0 and right for a 1 and same for next bit.
But no, this won't work since you don't care about the other boolean
columns, solong as the ones your looking for are true! To clarify, if you
don't care about the first column/bit, values from both the left and the
right parts of the binary tree can be valid.
Wild guess: base the ordering on the number of bits and then try to
narrow it down in the select with an equal operator.
SELECT * FROM TABLE
WHERE bitstring > 7
AND bitstring = 7;
Wouldn't know how to get this to work though! :(
Oracle has a special index type for just this case. I think they call
them bitmap indexes.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org