How to get the row with the latest value in a
TIMESTAMPZ column? Is an index needed? Does an index change the strategy? Would behavior vary by database (I'm using Postgres 9.4)?
My app records data from a data feed. Another process endlessly queries to get the latest freshest entry. Older data may appear on occasion from secondary sources. So the most recently inserted rows are usually, but not necessarily, the freshest data.
I am using this kind of SQL where
when_ is a
TIMESTAMP WITH TIME ZONE column:
SELECT * FROM my_table_ ORDER BY when_ DESC LIMIT 1 ;
This code works (if no NULL values in data!). But with a possible couple million rows, and a query every 10 seconds, I'm concerned about performance.
Without any index on
when_ column, does this statement require a full scan of all rows?
Does adding an index change the performance? Will Postgres automatically scan the index to locate the most recent row, or must I do something to make an index scan happen?
With an index on
when_ column, should I change this SQL to use some other approach/strategy of query?
Is there some other way to gather freshly inserted rows? I'm using UUID rather than SERIAL types for my primary key, and may federate data amongst multiple database instances, so that rules out checking for ever-increasing integer numbers.