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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
