Thread: 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