Re: selecting rows older than X, ensuring index is used
От | Oliver Elphick |
---|---|
Тема | Re: selecting rows older than X, ensuring index is used |
Дата | |
Msg-id | 1124582907.2847.16.camel@linda обсуждение исходный текст |
Ответ на | selecting rows older than X, ensuring index is used (CSN <cool_screen_name90001@yahoo.com>) |
Ответы |
Re: selecting rows older than X, ensuring index is used
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: