Re: Slow query and indexes...

From: "Isak Hansen" <isak(dot)hansen(at)gmail(dot)com>
To: "Andrew Kroeger" <andrew(at)sprocks(dot)gotdns(dot)com>
Cc: "Jonas Henriksen" <jonas(dot)f(dot)henriksen(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Slow query and indexes...
Date: 2007-05-07 19:35:14
Message-ID: 6b9e1eb20705071235i76ac648bu2316a4fa49010e2e@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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
>

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Richard Broersma Jr 2007-05-07 19:51:58 Win32 upgrade from 8.2.3 -> 8.2.4: What about the Documentation?
Previous Message Paolo Saudin 2007-05-07 19:25:58 R: Postgres 8.3-dev