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

Hi,

I want to select records that haven't had an error
(logged to last_error) in the last 24 hours. My query
is:

select * from table1
where last_error is null
or extract(epoch from now()-last_error) > 86400;

I've created an index on last_error (timestamp with
time zone - can be NULL), then used EXPLAIN:

Seq Scan on table1  (cost=0.00..20.86 rows=217
width=72)
  Filter: ((last_error IS NULL) OR
(date_part('epoch'::text, (now() - last_error)) >
86400::double precision))

There are over 550 rows in table1, so it doesn't look
the index is being used. Is there a way to rewrite
this query so the index is used?

Thanks,
CSN


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

On Sat, 2005-08-20 at 15:33 -0700, CSN wrote:
> Hi,
>
> I want to select records that haven't had an error
> (logged to last_error) in the last 24 hours. My query
> is:
>
> 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.  How about

  WHERE last_error IS NULL OR
        last_error < (CURRENT_TIMESTAMP - '1 day'::INTERVAL)

which has the merit of being more readable.

> I've created an index on last_error (timestamp with
> time zone - can be NULL), then used EXPLAIN:
>
> Seq Scan on table1  (cost=0.00..20.86 rows=217
> width=72)
>   Filter: ((last_error IS NULL) OR
> (date_part('epoch'::text, (now() - last_error)) >
> 86400::double precision))
>
> There are over 550 rows in table1, so it doesn't look
> the index is being used. Is there a way to rewrite
> this query so the index is used?

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

--
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
   Do you want to know God?   http://www.lfix.co.uk/knowing_god.html

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