Re: Slow query and indexes...

From: "Jonas Henriksen" <jonas(dot)f(dot)henriksen(at)gmail(dot)com>
To: "Peter Eisentraut" <peter_e(at)gmx(dot)net>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Slow query and indexes...
Date: 2007-05-07 15:47:08
Message-ID: 51518a4f0705070847t388dbfc7h6ee25367b56dd3f9@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Well thanks, but that don't help me much.

I've tried setting an extra condition using datetime>(now() - '14
weeks'::interval)

explain analyze
SELECT max(date_time) FROM data_values
where date_time > (now() - '14 weeks'::interval)
GROUP BY data_logger_id;

HashAggregate (cost=23264.52..23264.55 rows=2 width=12) (actual
time=1691.447..1691.454 rows=3 loops=1)
-> Bitmap Heap Scan on data_values (cost=7922.08..21787.31
rows=295442 width=12) (actual time=320.643..951.043 rows=298589
loops=1)
Recheck Cond: (date_time > (now() - '98 days'::interval))
-> Bitmap Index Scan on data_values_data_date_time_index
(cost=0.00..7848.22 rows=295442 width=0) (actual time=319.708..319.708
rows=298589 loops=1)
Index Cond: (date_time > (now() - '98 days'::interval))
Total runtime: 1691.598 ms

However, when I switch to using datetime>(now() - '15 weeks'::interval) I get:
explain analyze
SELECT max(date_time) FROM data_values
where date_time > (now() - '15 weeks'::interval)
GROUP BY data_logger_id;

HashAggregate (cost=23798.26..23798.28 rows=2 width=12) (actual
time=3237.816..3237.823 rows=3 loops=1)
-> Seq Scan on data_values (cost=0.00..22084.62 rows=342728
width=12) (actual time=0.037..2409.234 rows=344111 loops=1)
Filter: (date_time > (now() - '105 days'::interval))
Total runtime: 3237.944 ms

Doing "SET enable_seqscan=off" speeds up the query and forces the use
of the index, but I dont really love that solution...

regards Jonas:))

On 5/7/07, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> Am Montag, 7. Mai 2007 15:53 schrieb Jonas Henriksen:
> > while if I add a GROUP BY data_logger the query uses a seq scan and a
> >
> > lot of time:
> > >> explain analyze SELECT max(date_time) FROM data_values GROUP BY
> > data_logger_id;
>
> I don't think there is anything you can do about this.
>
> --
> Peter Eisentraut
> http://developer.postgresql.org/~petere/
>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jim Nasby 2007-05-07 15:47:24 Re: Any "guide to indexes" exists?
Previous Message Jim Nasby 2007-05-07 15:44:35 Re: Slow query and indexes...