why sequential scan is used on indexed column ???

Started by Julius Tuskenisalmost 18 years ago8 messagesgeneral

julius.tuskenis@gmail.com

almost 18 years ago

Hello.

I have a question concerning performance. One of my queries take a long
to execute. I tried to do "explain analyse" and I see that the
sequential scan is being used, although I have indexes set on columns
that are used in joins. The question is - WHY, and how to change that
behavior???

The DBMS: pgSQL 8.1.4 on gentoo linux.

The query:

explain analyze
select *
FROM b_saskaita JOIN apsilankymai ON (aps_saskaita = sas_id)
where sas_subjektas = 20190

result:
"Hash Join (cost=5.17..10185.89 rows=6047 width=138) (actual
time=10698.539..10698.539 rows=0 loops=1)"
" Hash Cond: ("outer".aps_saskaita = "inner".sas_id)"
" -> Seq Scan on apsilankymai (cost=0.00..8618.50 rows=300350
width=42) (actual time=2121.310..6470.721 rows=300350 loops=1)"
" -> Hash (cost=5.14..5.14 rows=9 width=96) (actual
time=31.545..31.545 rows=1 loops=1)"
" -> Bitmap Heap Scan on b_saskaita (cost=2.03..5.14 rows=9
width=96) (actual time=31.473..31.489 rows=1 loops=1)"
" Recheck Cond: (sas_subjektas = 20190)"
" -> Bitmap Index Scan on idx_sas_subjektas
(cost=0.00..2.03 rows=9 width=0) (actual time=25.552..25.552 rows=1
loops=1)"
" Index Cond: (sas_subjektas = 20190)"
"Total runtime: 10698.780 ms"

The tables with indexes:

CREATE TABLE b_saskaita
(
sas_id serial NOT NULL,
sas_tevas integer,
sas_kreditas numeric(8,2) NOT NULL DEFAULT 0,
sas_statusas smallint NOT NULL DEFAULT 1,
sas_subjektas integer,
sas_kam_naudojama integer,
sas_pastaba character varying(100),
CONSTRAINT b_saskaita_pkey PRIMARY KEY (sas_id),
CONSTRAINT fk_sas_subjektas FOREIGN KEY (sas_subjektas)
REFERENCES subjektas (sub_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT fk_saskaitos_tevas FOREIGN KEY (sas_tevas)
REFERENCES b_saskaita (sas_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT
)
WITHOUT OIDS;
ALTER TABLE b_saskaita OWNER TO postgres;
GRANT ALL ON TABLE b_saskaita TO postgres;
GRANT ALL ON TABLE b_saskaita TO public;

CREATE INDEX fki_sas_subjektas
ON b_saskaita
USING btree
(sas_subjektas);

CREATE TABLE apsilankymai
(
aps_id serial NOT NULL,
aps_abonementas integer NOT NULL,
aps_atejo timestamp(0) without time zone NOT NULL,
aps_isejo timestamp(0) without time zone,
aps_ileidimas integer,
aps_zetonas integer NOT NULL,
aps_padalinys integer NOT NULL,
aps_saskaita integer,
aps_statusas smallint DEFAULT 0,
CONSTRAINT apsilankymai_pkey PRIMARY KEY (aps_id),
CONSTRAINT fk_apsilankymo_abonementas FOREIGN KEY (aps_abonementas)
REFERENCES subjekto_abonementai (sab_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT fk_apsilankymo_padalinys FOREIGN KEY (aps_padalinys)
REFERENCES padalinys (pad_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT fk_apsilankymo_saskaita FOREIGN KEY (aps_saskaita)
REFERENCES b_saskaita (sas_id) MATCH FULL
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT fk_apsilankymo_zetonas FOREIGN KEY (aps_zetonas)
REFERENCES zetonai (zet_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT
)
WITHOUT OIDS;
ALTER TABLE apsilankymai OWNER TO postgres;
GRANT ALL ON TABLE apsilankymai TO postgres;
GRANT ALL ON TABLE apsilankymai TO public;
COMMENT ON COLUMN apsilankymai.aps_ileidimas IS 'jei apsilankymas neturi
skaitytis - nurodoma kuris apsilankymas yra pagrindinis';
COMMENT ON COLUMN apsilankymai.aps_padalinys IS 'kuriame padalinyje
lankesi zmogus. reikalingas, kai norim skaiciuoti kartus zmoniu turinciu
abonementa keliuose klubuose';
COMMENT ON COLUMN apsilankymai.aps_statusas IS '0 - neiejes, 1 - viduje,
2 - isejes';

CREATE INDEX idx_aps_saskaita
ON apsilankymai
USING btree
(aps_saskaita);

Thank you in advance.

Julius Tuskenis