Thanks for a good answer, I'll try to find a workaround. The number of data_loggers will change, but not to frequently. I was actually hoping to make a view showing the latest data for each logger, maybe I can manage that with a stored procedure thingy... Regards, Jonas:)) On 5/7/07, Andrew Kroeger <andrew@xxxxxxxxxxxxxxxxxx> wrote:
Jonas Henriksen wrote: >>> explain analyze SELECT max(date_time) FROM data_values; > Goes fast and returns: In prior postgres versions, the planner could not take advantage of indexes with max() (nor min()) calculations. A workaround to this was (given an appropriate index) a query like: select date_time from data_values order by date_time desc limit 1; The planner in recent versions has been upgraded to recognize this case and basically apply the same workaround automatically. This is shown by the "Index Scan Backward" and "Limit" nodes in the plan you posted. >>> explain analyze SELECT max(date_time) FROM data_values GROUP BY > data_logger_id; I cannot think of a workaround like above that would speed this up. The planner modifications that work in the above case probably don't handle queries like this in the same way. > Tha table contains approx 765000 rows. It has three distinct > data_logger_id's. I can make quick queries on each of them using: > SELECT max(date_time) FROM data_values where data_logger_id=1 If your 3 distinct data_logger_id will never change (or if you can handle code changes if/when they do change), the following might provide what you are looking for: select max(date_time) from data_values where data_logger_id=1 union all select max(date_time) from data_values where data_logger_id=2 union all select max(date_time) from data_values where data_logger_id=3 If that works for you, you may also be able to eliminate the (data_logger_id, date_time) index if no other queries need it (i.e. you added it in an attempt to speed up this specific case). Hope this helps. Andrew
