Re: Slow query and indexes...

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).

Naive question, but how would an index on (date_time, data_logger_id)
affect things?

Say coupled with limit 3 for the above case, or the date interval condition.

Isak

>
> Hope this helps.
>
> Andrew
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>