Slow query and indexes...

Hi,

I'm trying to figure out how to make postgres utilize my indexes on a table.
this query:
>> explain analyze SELECT max(date_time) FROM data_values;
Goes fast and returns:

                                                 QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result  (cost=0.08..0.09 rows=1 width=0) (actual time=0.108..0.111
rows=1 loops=1)
   InitPlan
     ->  Limit  (cost=0.00..0.08 rows=1 width=8) (actual
time=0.090..0.092 rows=1 loops=1)
           ->  Index Scan Backward using
data_values_data_date_time_index on data_values  (cost=0.00..58113.06
rows=765121 width=8) (actual time=0.078..0.078 rows=1 loops=1)
                 Filter: (date_time IS NOT NULL)
 Total runtime: 0.204 ms
(6 rows)

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;

                                                QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=20171.82..20171.85 rows=3 width=12) (actual
time=3510.500..3510.506 rows=3 loops=1)
   ->  Seq Scan on data_values  (cost=0.00..16346.21 rows=765121
width=12) (actual time=0.039..1598.518 rows=765121 loops=1)
 Total runtime: 3510.634 ms
(3 rows)

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

I have an index on the date_time field and on the data_logger_id
field, and I ahve also tried to make an index with both date_time and
data_logger_id. Anyone have any idea whats going on, and suggestions
what I should do to speed up my query?


Regards Jonas:)))

Im using PostgreSQL 8.2.3 on windows xp.

My table:
CREATE TABLE data_values
(
  data_value_id serial NOT NULL,
  data_type_id integer NOT NULL,
  data_collection_id integer NOT NULL,
  data_logger_id integer NOT NULL,
  date_time timestamp without time zone NOT NULL,
  lat_wgs84 double precision NOT NULL,
  lon_wgs84 double precision NOT NULL,
  height integer NOT NULL,
  parallell integer NOT NULL DEFAULT 0,
  data_value double precision NOT NULL,
  sensor_id integer,
  CONSTRAINT data_values_pkey PRIMARY KEY (data_value_id),
  CONSTRAINT data_values_data_collection_id_fkey FOREIGN KEY
(data_collection_id)
      REFERENCES data_collections (data_collection_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT data_values_data_logger_id_fkey FOREIGN KEY (data_logger_id)
      REFERENCES data_loggers (data_logger_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT data_values_data_type_id_fkey FOREIGN KEY (data_type_id)
      REFERENCES data_types (data_type_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT data_values_sensor_id_fkey FOREIGN KEY (sensor_id)
      REFERENCES sensors (sensor_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT data_values_data_type_id_key UNIQUE (data_type_id,
data_logger_id, date_time, lat_wgs84, lon_wgs84, height, parallell)
);

CREATE INDEX data_values_data_date_time_index
  ON data_values
  USING btree
  (date_time);

CREATE INDEX data_values_data_logger_id_index
  ON data_values
  USING btree
  (data_logger_id);

CREATE INDEX data_values_time_logger_index
  ON data_values
  USING btree
  (data_logger_id, date_time);