index on id and created_at
Hi,
If I have a table like...
CREATE TABLE apa
(
objectid SERIAL PRIMARY KEY NOT NULL
,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
,somedata text
)
WITHOUT OIDS;
...where if rowX har higher objectid than rowY, it is implied that rowX
has a later created_at then rowY.
select
objectid
from
apa
where
created_at > now() - interval '1 day'
order by
objectid desc;
In this select, it would have been nice to dodge the full table scan
without adding an index to created_at. Is this possible somehow?
Best regards,
Marcus
On Thu, Sep 11, 2008 at 10:08 AM, Marcus Engene <mengpg2@engene.se> wrote:
select
objectid
from
apa
where
created_at > now() - interval '1 day'
order by
objectid desc;In this select, it would have been nice to dodge the full table scan without
adding an index to created_at. Is this possible somehow?
Not really. It's a choice of one or the other.
On Thu, Sep 11, 2008 at 06:08:15PM +0200, Marcus Engene wrote:
In this select, it would have been nice to dodge the full table scan
without adding an index to created_at. Is this possible somehow?
yes.
1. drop condition on created_at
2. run the query as cursor
3. fetch rows from cursor until you will get row that has unsatisfactory
created_at.
Best regards,
depesz
--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007