Re: Why is NULL not indexable?

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: Daniel ?kerud <zilch(at)home(dot)se>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Why is NULL not indexable?
Date: 2001-06-27 01:19:55
Message-ID: 20010627111955.B10412@svana.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Ryan Mahoney 2001-06-27 01:24:24 Re: Complicated query... is there a simpler way?
Previous Message Rob Arnold 2001-06-27 00:56:16 Re: Capitalizing First Letter of Every Word