How to avoid using sequential scan - Mailing list pgsql-general
| From | Victor Adolfsson |
|---|---|
| Subject | How to avoid using sequential scan |
| Date | |
| Msg-id | 9a6439440702120426r5888c3b7o32b59c5490cc43d3@mail.gmail.com Whole thread Raw |
| Responses |
Re: How to avoid using sequential scan
(Richard Huxton <dev@archonet.com>)
|
| List | pgsql-general |
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
best regards
Victor Adolfsson
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.257 rows=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.1 20060525 (Red Hat 4.1.1-1)
(1 row)
(pgadmin 1.4.3)
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.257 rows=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.1 20060525 (Red Hat 4.1.1-1)
(1 row)
(pgadmin 1.4.3)
best regards
Victor Adolfsson
pgsql-general by date: