Re: selecting rows older than X, ensuring index is used

Oliver Elphick <olly@lfix.co.uk> writes:
> On Sat, 2005-08-20 at 15:33 -0700, CSN wrote:
>> select * from table1
>> where last_error is null
>> or extract(epoch from now()-last_error) > 86400;

> I don't know whether the planner would recognise that it could use an
> index on that condition.

The "is null" isn't indexable, and an OR with a nonindexable condition
kills the entire point of considering an indexscan.  (If you have to do
a seqscan anyway, there's no point in doing an indexscan too.)

You could probably make it work if you created a partial index with the
condition "last_error IS NULL"; then the planner could combine an
indexscan on that with an indexscan on a regular last_error index
(given refactoring of the other condition as Oliver recommends).

> The estimate is that nearly half of those 550 rows will be returned, so
> a sequential scan would probably be chosen in any case.

Yeah.  Unless it's going to be a lot more selective than that, the
indexscan approach will be a loser anyway.

            regards, tom lane