>Alvaro Herrera
> On Tue, Oct 19, 2004 at 11:22:31PM +0100, Simon Riggs wrote:
>
> > I was thinking about this recently, then realised that building the
bitmap
> > would not be as easily, since PostgreSQL doesn't index null values. That
> > would mean that the sets of CTIDs in each index would be disjoint. My
> > thinking about dynamic bitmaps came from Teradata, which does index null
> > values.
>
> Huh, you are wrong.
Always happy to learn. Thanks for letting me know.
> At least btree does index null values, and one
> other index method does too. The other two index methods don't. What
> doesn't work is using an index with the IS NULL construct, because it's
> not an operator. Maybe that can be fixed by some other means ... some
> parser magic perhaps.
The manual says this (CREATE INDEX)
"Indexes are not used for IS NULL clauses by default. The best way to use
indexes in such cases is to create a partial index using an IS NULL
comparison. "
Perhaps we can find a better way of wording this to explain what actually
occurs, which after your comments, I'm less clear on than I was before.
Could you clarify further, so we can update the documentation to be very
specific, or at least clearer.
> > Or would you:
> > - copy aside and sort the indexes on CTID
> > - merge join them all to find matching CTIDs
> > - probe into the main table
>
> IIRC part of the trick was to build bitmaps to apply bitwise-AND/OR
> operators. This allows to use multiple indexes for one scan, for
> example.
Yes, an implication of my question was "and would that then give greater
overhead for >2 indexes...
> I don't understand your comment about read only tables ...
These are restrictions on the Oracle implementation.
If you had a larger data warehouse table that grew over time, then typically
the older data wouldn't change much and so a "read-only" technique could be
sensibly applied.
Best Regards, Simon Riggs
