On Fri, 13 Jun 2003, Ernest E Vogelsinger wrote:
> At 23:56 12.06.2003, Stephan Szabo said:
> --------------------[snip]--------------------
> Nope, still uses the wrong index, but as I said to Dimitry that's my least
> problem ;-)
>
> >> 2) Why is NO index used for the second query, the only difference being in
> >> the constraint value (owid is set vs. owid is null)?
> >
> >IS NULL is not considered an indexable condition currently (there are past
> >discussions and hackarounds in the archives)
>
> Hmm - I'm not into hackarounds on a production server, really. I'll rather
> modify the approach the application takes.
Well, I'm considering the col IS NULL partial index as a hackaround. I
gather it doesn't use that index even when you set enable_seqscan=off as
well. Hmm, I've seen that work on simpler cases I think... Yeah, on a
simple table of ints I can get it to do just unique/index-scan. Hmm.
> >> 3) Why does it use id_dictid for the second query when forced to, and not
> >> id_owid or id_dowid?
> >
> >As for #2, it doesn't think it can use an index with owid in the front.
>
> Makes perfectly sense since nulls can't be indexed *sigh*
>
> Anyone know why this decision has been taken?
It's not the nulls precisely, it's the IS NULL predicate that doesn't
really fit into the mostly nicely flexible index system. :( There've been
discussions about this, I don't really remember details though.