Re: Slow query and indexes...

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/
>