Re: Would an index benefit select ... order by?

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