Re: Slow query and indexes... - Mailing list pgsql-general

From Jonas Henriksen
Subject Re: Slow query and indexes...
Date
Msg-id 51518a4f0705070847t388dbfc7h6ee25367b56dd3f9@mail.gmail.com
Whole thread Raw
In response to Re: Slow query and indexes...  (Peter Eisentraut <peter_e@gmx.net>)
Responses Re: Slow query and indexes...
List 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@gmx.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/
>

pgsql-general by date:

Previous
From: Jim Nasby
Date:
Subject: Re: Slow query and indexes...
Next
From: Jim Nasby
Date:
Subject: Re: Any "guide to indexes" exists?