why sequential scan is used on indexed column ???

Julius Tuskenis <julius.tuskenis@xxxxxxxxx> · Sat, 14 Jun 2008 17:43:46 +0300

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