[justinsb]

If you have an index on the date, then selecting the last 10 orders will be very cheap, and there's no reason not to do that in a relational database - it should be near-instantaneous.

The trade-off here is that every index you add makes insertion a little bit slower, so you don't normally want to just add an index for every query.

[rmetzler]

Ok, thank you. Now you got me thinking, what did I do wrong with this Oracle 9 DB several years ago? :/

[justinsb]

Perhaps you selected the last ten orders for a particular customer, or something like that. In that case, the query planner would have to choose whether to scan the entire table, or to scan the timestamp index and then filter by customer. If you had a lot of customers, it is reasonable to choose the table scan.

Of course, for that query, you could create an index on customer_id & timestamp, and then it would be near-instant again!