I have small database. However the following query takes 38 (!) seconds to
run.
How to speed it up (preferably not changing table structures but possibly
creating indexes) ?
Andrus.
set search_path to public,firma1;
explain analyze select bilkaib.summa from BILKAIB join KONTO CRKONTO ON
bilkaib.cr=crkonto.kontonr AND
crkonto.iseloom='A'
join KONTO DBKONTO ON bilkaib.db=dbkonto.kontonr AND
dbkonto.iseloom='A'
left join klient on bilkaib.klient=klient.kood
where ( bilkaib.cr LIKE '3'||'%' OR
bilkaib.db LIKE '3'||'%' )
AND bilkaib.kuupaev BETWEEN '2006-01-01' AND '2006-03-31'
AND ( kuupaev='20060101' OR (cr!='00' and db!='00'))
AND ( 3 IN(2,3) or (NOT bilkaib.ratediffer and (
TRIM(bilkaib.masin)='' or bilkaib.masin IS NULL or
bilkaib.alusdok not in ('KV', 'DU', 'DJ') or
bilkaib.andmik is NULL or bilkaib.alusdok is NULL or
substring(andmik from 1 for 9)!='Kursivahe'
))) and
( position(bilkaib.laustyyp IN 'x')=0 or
bilkaib.laustyyp is null or bilkaib.laustyyp=' ')
"Nested Loop Left Join (cost=23.30..1964.10 rows=1 width=10) (actual
time=7975.470..38531.724 rows=3151 loops=1)"
" -> Nested Loop (cost=23.30..1958.08 rows=1 width=26) (actual
time=7975.407..37978.718 rows=3151 loops=1)"
" Join Filter: ("inner".cr = "outer".kontonr)"
" -> Seq Scan on konto crkonto (cost=0.00..23.30 rows=1 width=44)
(actual time=0.135..13.913 rows=219 loops=1)"
" Filter: (iseloom = 'A'::bpchar)"
" -> Hash Join (cost=23.30..1934.64 rows=11 width=40) (actual
time=1.650..155.734 rows=3151 loops=219)"
" Hash Cond: ("outer".db = "inner".kontonr)"
" -> Index Scan using bilkaib_kuupaev_idx on bilkaib
(cost=0.00..1897.10 rows=2826 width=54) (actual time=1.628..111.216
rows=3151 loops=219)"
" Index Cond: ((kuupaev >= '2006-01-01'::date) AND
(kuupaev <= '2006-03-31'::date))"
" Filter: (((cr ~~ '3%'::text) OR (db ~~ '3%'::text)) AND
((kuupaev = '2006-01-01'::date) OR ((cr <> '00'::bpchar) AND (db <>
'00'::bpchar))) AND (("position"('x'::text, (laustyyp)::text) = 0) OR
(laustyyp IS NULL) OR (laustyyp = ' '::bpc (..)"
" -> Hash (cost=23.30..23.30 rows=1 width=44) (actual
time=2.278..2.278 rows=219 loops=1)"
" -> Seq Scan on konto dbkonto (cost=0.00..23.30 rows=1
width=44) (actual time=0.017..1.390 rows=219 loops=1)"
" Filter: (iseloom = 'A'::bpchar)"
" -> Index Scan using klient_pkey on klient (cost=0.00..6.01 rows=1
width=52) (actual time=0.138..0.158 rows=1 loops=3151)"
" Index Cond: ("outer".klient = klient.kood)"
"Total runtime: 38561.745 ms"
CREATE TABLE firma1.bilkaib
(
id int4 NOT NULL DEFAULT nextval('bilkaib_id_seq'::regclass),
kuupaev date NOT NULL,
db char(10) NOT NULL,
dbobjekt char(10),
cr char(10) NOT NULL,
crobjekt char(10),
summa numeric(14,2) NOT NULL,
raha char(3) NOT NULL,
masin char(5),
klient char(12),
alusdok char(2),
dokumnr int4 NOT NULL DEFAULT nextval('bilkaib_dokumnr_seq'::regclass),
db2objekt char(10),
cr2objekt char(10),
db3objekt char(10),
db4objekt char(10),
db5objekt char(10),
db6objekt char(10),
db7objekt char(10),
db8objekt char(10),
db9objekt char(10),
cr3objekt char(10),
cr4objekt char(10),
cr5objekt char(10),
cr6objekt char(10),
cr7objekt char(10),
cr8objekt char(10),
cr9objekt char(10),
exchrate numeric(13,8),
doknr char(25),
andmik text,
laustyyp char(1),
ratediffer ebool,
adoknr char(25),
jarjeknr numeric(7),
CONSTRAINT bilkaib_pkey PRIMARY KEY (id),
CONSTRAINT bilkaib_alusdok_fkey FOREIGN KEY (alusdok)
REFERENCES firma1.alusdok (alusdok) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_cr2objekt_fkey FOREIGN KEY (cr2objekt)
REFERENCES firma1.yksus2 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_cr3objekt_fkey FOREIGN KEY (cr3objekt)
REFERENCES firma1.yksus3 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_cr4objekt_fkey FOREIGN KEY (cr4objekt)
REFERENCES firma1.yksus4 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_cr5objekt_fkey FOREIGN KEY (cr5objekt)
REFERENCES firma1.yksus5 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_cr6objekt_fkey FOREIGN KEY (cr6objekt)
REFERENCES firma1.yksus6 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_cr7objekt_fkey FOREIGN KEY (cr7objekt)
REFERENCES firma1.yksus7 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_cr8objekt_fkey FOREIGN KEY (cr8objekt)
REFERENCES firma1.yksus8 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_cr9objekt_fkey FOREIGN KEY (cr9objekt)
REFERENCES firma1.yksus9 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_cr_fkey FOREIGN KEY (cr)
REFERENCES firma1.konto (kontonr) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_crobjekt_fkey FOREIGN KEY (crobjekt)
REFERENCES firma1.yksus1 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_db2objekt_fkey FOREIGN KEY (db2objekt)
REFERENCES firma1.yksus2 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_db3objekt_fkey FOREIGN KEY (db3objekt)
REFERENCES firma1.yksus3 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_db4objekt_fkey FOREIGN KEY (db4objekt)
REFERENCES firma1.yksus4 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_db5objekt_fkey FOREIGN KEY (db5objekt)
REFERENCES firma1.yksus5 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_db6objekt_fkey FOREIGN KEY (db6objekt)
REFERENCES firma1.yksus6 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_db7objekt_fkey FOREIGN KEY (db7objekt)
REFERENCES firma1.yksus7 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_db8objekt_fkey FOREIGN KEY (db8objekt)
REFERENCES firma1.yksus8 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_db9objekt_fkey FOREIGN KEY (db9objekt)
REFERENCES firma1.yksus9 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_db_fkey FOREIGN KEY (db)
REFERENCES firma1.konto (kontonr) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_dbobjekt_fkey FOREIGN KEY (dbobjekt)
REFERENCES firma1.yksus1 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_klient_fkey FOREIGN KEY (klient)
REFERENCES firma1.klient (kood) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_raha_fkey FOREIGN KEY (raha)
REFERENCES raha (raha) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT bilkaib_id_check CHECK (id > 0)
)
WITHOUT OIDS;
CREATE INDEX bilkaib_dokumnr_idx ON firma1.bilkaib USING btree (dokumnr);
CREATE INDEX bilkaib_kuupaev_idx ON firma1.bilkaib USING btree (kuupaev);
CREATE TABLE firma1.konto
(
kontonr char(10) NOT NULL,
tyyp char(1) NOT NULL,
klienkaupa ebool,
arvekaupa ebool,
objekt1 char(1),
objekt2 char(1),
objekt3 char(1),
objekt4 char(1),
objekt5 char(1),
objekt6 char(1),
objekt7 char(1),
objekt8 char(1),
objekt9 char(1),
tekst char(55),
rustekst char(55),
engtekst char(55),
fintekst char(55),
lvltekst char(55),
raha char(3) NOT NULL,
kontoklass char(10),
grupp char(13),
klient char(12),
iseloom char(1),
kontokl2 char(10),
kontokl3 char(10),
eelklassif char(10),
klassif8 char(10),
rid3obj char(1),
rid4obj char(1),
koondkonto char(10),
kaibedrida char(6),
CONSTRAINT konto_pkey PRIMARY KEY (kontonr),
CONSTRAINT konto_klassif8_fkey FOREIGN KEY (klassif8)
REFERENCES firma1.yksus8 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT konto_klient_fkey FOREIGN KEY (klient)
REFERENCES firma1.klient (kood) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT konto_kontokl2_fkey FOREIGN KEY (kontokl2)
REFERENCES bilskeem2 (kontoklass) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT konto_kontokl3_fkey FOREIGN KEY (kontokl3)
REFERENCES bilskeem3 (kontoklass) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT konto_kontoklass_fkey FOREIGN KEY (kontoklass)
REFERENCES bilskeem1 (kontoklass) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT konto_raha_fkey FOREIGN KEY (raha)
REFERENCES raha (raha) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE
)
WITHOUT OIDS;
CREATE TRIGGER konto_trig BEFORE INSERT OR UPDATE OR DELETE
ON firma1.konto FOR EACH STATEMENT EXECUTE PROCEDURE setlastchange();
CREATE TABLE firma1.klient
(
kood char(12) NOT NULL DEFAULT nextval('klient_kood_seq'::regclass),
nimi char(70),
a_a char(35),
p_kood char(10),
regnr char(12),
vatpayno char(15),
piirkond char(30),
postiindek char(10),
tanav char(30),
kontaktisi char(30),
telefon char(25),
faks char(25),
email char(60),
infomail char(60),
wwwpage char(50),
liik char(10),
viitenr char(20),
riik char(20),
riik2 char(2),
riigikood char(3),
hinnak char(5),
erihinnak char(5),
myygikood char(4),
objekt2 char(10),
objekt5 char(10),
objekt7 char(10),
maksetin char(5),
omakseti char(5),
krediit numeric(12,2),
ostukredii numeric(12,2),
masin char(5),
info text,
maksja char(12),
"timestamp" char(14) NOT NULL DEFAULT to_char(now(),
'YYYYMMDDHH24MISS'::text),
atimestamp char(14) NOT NULL DEFAULT to_char(now(),
'YYYYMMDDHH24MISS'::text),
elanikud numeric(3),
pindala numeric(7,2),
grmaja char(10),
apindala numeric(7,2),
kpindala numeric(7,2),
idmakett char(36),
tulemus char(100),
omandisuhe char(1),
username char(10),
changedby char(10),
parool char(20),
hinnaale char(4),
mitteakt ebool,
kontakteer date,
klikaart char(16),
mhprotsent numeric(5,1),
aadress text,
swift char(20),
pankaad char(20),
_nimi char(70),
CONSTRAINT klient_pkey PRIMARY KEY (kood),
CONSTRAINT klient_changedby_fkey FOREIGN KEY (changedby)
REFERENCES kasutaja (kasutaja) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT klient_grmaja_fkey FOREIGN KEY (grmaja)
REFERENCES firma1.yksus1 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT klient_hinnak_fkey FOREIGN KEY (hinnak)
REFERENCES firma1.hkpais (hinnak) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT klient_idmakett_fkey FOREIGN KEY (idmakett)
REFERENCES makett (guid) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT klient_liik_fkey FOREIGN KEY (liik)
REFERENCES klliik (liik) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT klient_maksetin_fkey FOREIGN KEY (maksetin)
REFERENCES maksetin (maksetin) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT klient_maksja_fkey FOREIGN KEY (maksja)
REFERENCES firma1.klient (kood) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT klient_myygikood_fkey FOREIGN KEY (myygikood)
REFERENCES firma1.myygikoo (myygikood) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT klient_objekt2_fkey FOREIGN KEY (objekt2)
REFERENCES firma1.yksus2 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT klient_objekt5_fkey FOREIGN KEY (objekt5)
REFERENCES firma1.yksus5 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT klient_objekt7_fkey FOREIGN KEY (objekt7)
REFERENCES firma1.yksus7 (yksus) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT klient_omakseti_fkey FOREIGN KEY (omakseti)
REFERENCES maksetin (maksetin) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT klient_p_kood_fkey FOREIGN KEY (p_kood)
REFERENCES pank (kood) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT klient_riik2_fkey FOREIGN KEY (riik2)
REFERENCES riik (kood) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT klient_username_fkey FOREIGN KEY (username)
REFERENCES kasutaja (kasutaja) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT klient_email_check CHECK (rtrim(email::text) ~*
E'^[^@]*@(?:[^@]*\\.)?[a-z0-9_-]+\\.(?:a[defgilmnoqrstuwz]|b[abdefghijmnorstvwyz]|c[acdfghiklmnoruvxyz]|d[ejkmoz]|e[ceghrst]|f[ijkmorx]|g[abdefhilmnpqrstuwy]|h[kmnrtu]|i[delnoqrst]|j[mop]|k[eghimnprwyz]|l[abcikrstuvy]|m[acdghklmnopqrstuvwxyz]|n[acefgilopruz]|om|p[aefghklmnrtwy]|qa|r[eouw]|s[abcdeghijklmnortvyz]|t[cdfghjkmnoprtvwz]|u[agkmsyz]|v[aceginu]|w[fs]|y[etu]|z[amw]|edu|com|net|org|gov|mil|info|biz|coop|museum|aero|name|pro|mobi|arpa)$'::text)
)
WITHOUT OIDS;
CREATE UNIQUE INDEX klient_nimi_unique_idx
ON firma1.klient USING btree (lower(nimi::text));
Server:
"PostgreSQL 8.1.3 on i386-portbld-freebsd5.4, compiled by GCC cc (GCC) 3.4.2
[FreeBSD] 20040728"
Client: ODBC driver in XP