[GENERAL] How to avoid using sequential scan

[Victor Adolfsson]

Hi

My query is using a sequential scan and not an index scan even though that I
have indexes defined on the foreign keys.
This cases my query to take a long long time (10750.687 ms) when it should
have been completed in less than 1 second.
Any ideas on what may be the cause of this? I have done a re-index.

Below, I'm including the sql query, the sql schema with indexes and the
results of the explain, as well as the postgresql version.

-- SQLQUERY
select datetimestamptz, description from unithistory inner join event on
event_id=event.id;


-- SQLSCHEMA

CREATE TABLE unithistory
(
 id serial NOT NULL,
 datetimestamptz timestamptz,
 data varchar(255),
 unit_id int4,
 event_id int4,
 enduser_id int4,
 installation_id int4,
 application_id int4,
 occurence_id int4,
 CONSTRAINT unithistory_pkey PRIMARY KEY (id),
 CONSTRAINT unithistory_application_id_fkey FOREIGN KEY (application_id)
     REFERENCES application (id) MATCH SIMPLE
     ON UPDATE NO ACTION ON DELETE NO ACTION,
 CONSTRAINT unithistory_enduser_id_fkey FOREIGN KEY (enduser_id)
     REFERENCES enduser (id) MATCH SIMPLE
     ON UPDATE NO ACTION ON DELETE NO ACTION,
 CONSTRAINT unithistory_event_id_fkey FOREIGN KEY (event_id)
     REFERENCES event (id) MATCH SIMPLE
     ON UPDATE NO ACTION ON DELETE NO ACTION,
 CONSTRAINT unithistory_installation_id_fkey FOREIGN KEY (installation_id)
     REFERENCES installation (id) MATCH SIMPLE
     ON UPDATE NO ACTION ON DELETE NO ACTION,
 CONSTRAINT unithistory_unit_id_fkey FOREIGN KEY (unit_id)
     REFERENCES unit (id) MATCH SIMPLE
     ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITHOUT OIDS;
ALTER TABLE unithistory OWNER TO precondbuser;
COMMENT ON TABLE unithistory IS 'Where all events that happens on a unit are
stored.';


-
-- Index: idx_unithistory_event_id

CREATE INDEX idx_unithistory_event_id
 ON unithistory
 USING btree
 (event_id);


CREATE TABLE event
(
 id serial NOT NULL,
 description varchar(50), -- The name of an event
 longdescription text,
 severity_id int4,
 CONSTRAINT event_pkey PRIMARY KEY (id),
 CONSTRAINT event_severity_id_fkey FOREIGN KEY (severity_id)
     REFERENCES severity (id) MATCH SIMPLE
     ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITHOUT OIDS;



-- EXPLAIN RESULTS
Hash Join  (cost= 1.12..82296.20 rows=2396163 width=26) (actual time=
24.885..8838.418 rows=2396163 loops=1)
 Hash Cond: (unithistory.event_id = event.id)
 ->  Seq Scan on unithistory  (cost=0.00..46352.63 rows=2396163 width=12)
(actual time=6.580..3597.683 rows=2396163 loops=1)
 ->  Hash  (cost=1.10..1.10 rows=10 width=22) (actual
time=18.257..18.257rows=10 loops=1)
       ->  Seq Scan on event  (cost=0.00..1.10 rows=10 width=22) (actual
time=18.223..18.235 rows=10 loops=1)
Total runtime: 10750.687 ms


VERSION
select version();
                                             version
---------------------------------------------------------------------------------------------------

PostgreSQL 8.2.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
4.1.120060525 (Red Hat
4.1.1-1)
(1 row)

(pgadmin 1.4.3)


best regards
Victor Adolfsson