From: | CSN <cool_screen_name90001(at)yahoo(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | selecting rows older than X, ensuring index is used |
Date: | 2005-08-20 22:33:01 |
Message-ID: | 20050820223301.34430.qmail@web52902.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Oliver Elphick | 2005-08-21 00:08:27 | Re: selecting rows older than X, ensuring index is used |
Previous Message | Michael Fuhr | 2005-08-20 15:46:56 | Re: Beginner - Help for pgsql 8.0.3 win32 platform |