Martijn van Oosterhout <kleptog@svana.org> writes:
>> You could stick a phony value into the correlation datum.
> Ah, that would do it. Would need to experiment. Is this in 7.1 or 7.2?
Current development sources (7.2-to-be).
>> I'd like to see the partial-index support cranked up again, for sure.
>> But how does that solve your problem? I don't see the connection.
> Because the queries are of the form ID1 = 'xxxx' and ID2 is null. So,
> improving the index scan would make it use the index for the first clause
> and scan for the second (nulls don't appear in indicies, right?)
btrees do actually store nulls, but no one's gotten around to fixing the
rest of the index machinery to make an IS NULL clause indexable. I
don't think there's any reason it couldn't work, just needs someone to
go through the code and make it happen.
> With a partial index on the ID2 is null clause it could scan that index and
> look for tuples with match the first.
Hm. The old index-predicate-check code is pretty limited as to what it
can recognize. Again, I'm not sure that an IS NULL clause would be
recognized as implying the partial-index condition --- at least not
without work. But hey, if you can fix the partial-index code at all,
that shouldn't stop you ;-)
Offhand I'd say that teaching the index machinery to allow IS [NOT] NULL
to be indexable would be a better answer to your problem for less work.
A short-term answer would be to add a boolean column that could be used
in place of the NULL test on ID2, and to index (ID1,boolean) in place
of what you're doing now.
> Does the planner take into account that since the total size of the database
> is less than the total amount of memory, a lot of the database is likely to
> be cached?
It tries. Whether the cost model for this has anything to do with the
real behavior of your system is another question.
> Talking about statistics, is there anywhere that counts the number of times
> an index has been used? So I can check to see if all my indicies are
> worthwhile.
Not at the moment, but I think Jan has some plans for a statistics
module that might be able to do that.
regards, tom lane
