Re: Slow query and indexes...

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