Re: Why is NULL not indexable?

On Tue, Jun 26, 2001 at 06:40:57PM +0200, Daniel ?kerud wrote:
> I was thinking about what this actually meant and came to the conclusion
> that having
> SELECT * FROM foo WHERE bar IS NULL
> would always result in a sequential scan.
>
> Or does it mean anything else?

No, that's exactly what it means. That's why I'm looking at it because it's
something I would like to change, but I havn't quite worked out how.

The thing is, if I converted all the nulls to empty strings they would be
indexable but the statistics would be terribly skewed giving a seqential
scan anyway.

I think I need to rethink this anyway...

> > > I can't work out what the 'strategy' bit refers to. All I can find in
> the
> > > source code is references to tables of magic numbers. I guess what I
> really
> > > want to know is, how hard would it be to fix?
> >
> > I believe the main problem is that IS NULL and IS NOT NULL are not
> > operators (they don't have pg_operator entries), and all of the planning
> > and indexscan execution machinery is designed around operators. Binary
> > operators, at that.
> >
> > It's possible that this could be hacked around by creating dummy
> > pg_operator entries for them, but my bet is that cleaning up the loose
> > ends and no-longer-valid coding assumptions would be a nontrivial task.
> >
> > regards, tom lane
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Martijn van Oosterhout <kleptog(at)svana(dot)org>
http://svana.org/kleptog/
- Artificial Intelligence is the science of making computers that behave
- like the ones in the movies.