why sequential scan is used on indexed column ??? - Mailing list pgsql-general

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