Would an index benefit select ... order by?

am Sat, dem 03.11.2007, um 11:09:05 +0400 mailte rihad folgendes:

Hi,

Should an index be used on a created_at timestamp column if you know you
will be using "ORDER BY created_at ASC|DESC" from time to time?

Yes. And you should use EXPLAIN.

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

Should an index be used on a created_at timestamp column if you know you
will be using "ORDER BY created_at ASC|DESC" from time to time?

Yes.

Thanks. This is stated explicitly in 8.3 docs (as opposed to 8.2)

http://www.postgresql.org/docs/8.3/static/indexes-ordering.html

And you should use EXPLAIN.

You mean Postgres wouldn't *always* use created_at's index with such
access patterns on a big table (even if one exists):

select * from foo order by created_at desc;

?

Mind you the distribution of created_at values are going to be as
different as the time is (i.e. almost as many different values as there
are tables in the row).

rihad <rihad@mail.ru> writes:

You mean Postgres wouldn't *always* use created_at's index with such
access patterns on a big table (even if one exists):

select * from foo order by created_at desc;

No, it wouldn't necessarily, and that's a good thing. A full-table
indexscan can often be slower than a sort because of inefficient disk
access patterns. The planner will estimate the cost of each possibility
and pick the one that looks cheaper.

regards, tom lane

You mean Postgres wouldn't *always* use created_at's index with such
access patterns on a big table (even if one exists):

select * from foo order by created_at desc;

No, it wouldn't necessarily, and that's a good thing. A full-table
indexscan can often be slower than a sort because of inefficient disk
access patterns. The planner will estimate the cost of each possibility
and pick the one that looks cheaper.

What if it's really a limited select:

select * from foo order by created_at desc limit <SCREEN_AT_A_TIME>;

because this is what I meant initially (sorry), would Postgres always
use index to get at sorted created_at values, so I don't *have* to
create the index? I think maintaining the index has its own penalty so
in my upcoming project I'm evaluating the option of skipping defining
one entirely unless absolutely necessary.

On Mon, Nov 05, 2007 at 10:09:12AM +0400, rihad wrote:

What if it's really a limited select:

select * from foo order by created_at desc limit <SCREEN_AT_A_TIME>;

because this is what I meant initially (sorry), would Postgres always
use index to get at sorted created_at values, so I don't *have* to
create the index?

Postgres would probably use the index in this case. In general,
postgres plan's the execution of a query whichever way the statistics
about the data indicate are likely to make it proceed the fastest.

I think maintaining the index has its own penalty so
in my upcoming project I'm evaluating the option of skipping defining
one entirely unless absolutely necessary.

It's always a balancing act. If your code is spending the majority of
the time running the above select (and postgres thinks that an index
scan is best) then you're better off with the index. If your code is
spending the majority of the time inserting data (and hence updating
indexes) then you're probably better off without the index. You need to
know your access patterns and determine which is best for you.

I'd generally leave indexes out until I know that I need them. Indexes
are, after all, just a performance hack and therefore the root of all
evil! :)

Sam