Re: Slow query and indexes...

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(at)sprocks(dot)gotdns(dot)com> 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
>
>