Query performance with small data base

On Thu, 17 Oct 2002, pginfo wrote:

Hi all,

From couple of days I make some tests for postgresql performance.

The results are not optimistic for the moment.
The idea of tests is to view if postgres is good for our new project.

The test platform:

RH 7.3 on Dual P III 1 GHz , 1GB RAM.

The test query:

explain analyze select
S.IDS_NUM,S.OP,S.KOL,S.OTN_MED,S.CENA,S.DDS,S.KURS,S.TOT,S.DTO,S.PTO,S.DTON,MED.MNAME
AS MEDNAME,N.MNAME AS NOMENNAME,N.NUM AS NNUM,S.PART,S.IZV from A_DOC
D left outer join A_KLIENTI KL ON(D.IDS_KO=KL.IDS) , A_SKLAD S left
outer join A_NOMEN N ON(S.IDS_NUM=N.IDS) left outer join A_MED MED ON
(N.OSN_MED=MED.IDS ) WHERE S.FID=0 AND S.IDS_DOC=D.IDS AND
D.DATE_OP >= 8353 AND D.DATE_OP <= 9983 ORDER BY S.IDS_NUM,S.PART,S.OP
;
NOTICE: QUERY PLAN:

Hmm, what are the table schemas and how many rows are in the full tables?

pginfo <pginfo@t1.unisoftbg.com> writes:

explain analyze select
S.IDS_NUM,S.OP,S.KOL,S.OTN_MED,S.CENA,S.DDS,S.KURS,S.TOT,S.DTO,S.PTO,S.DTON,MED.MNAME
AS MEDNAME,N.MNAME AS NOMENNAME,N.NUM AS NNUM,S.PART,S.IZV from A_DOC
D left outer join A_KLIENTI KL ON(D.IDS_KO=KL.IDS) , A_SKLAD S left
outer join A_NOMEN N ON(S.IDS_NUM=N.IDS) left outer join A_MED MED ON
(N.OSN_MED=MED.IDS ) WHERE S.FID=0 AND S.IDS_DOC=D.IDS AND
D.DATE_OP >= 8353 AND D.DATE_OP <= 9983 ORDER BY S.IDS_NUM,S.PART,S.OP

Hmm, seems like most of the cycles are going into hash joins. What
explain result do you get if you do "set enable_hashjoin to off"?

regards, tom lane

Tom Lane wrote:

pginfo <pginfo@t1.unisoftbg.com> writes:

explain analyze select
S.IDS_NUM,S.OP,S.KOL,S.OTN_MED,S.CENA,S.DDS,S.KURS,S.TOT,S.DTO,S.PTO,S.DTON,MED.MNAME
AS MEDNAME,N.MNAME AS NOMENNAME,N.NUM AS NNUM,S.PART,S.IZV from A_DOC
D left outer join A_KLIENTI KL ON(D.IDS_KO=KL.IDS) , A_SKLAD S left
outer join A_NOMEN N ON(S.IDS_NUM=N.IDS) left outer join A_MED MED ON
(N.OSN_MED=MED.IDS ) WHERE S.FID=0 AND S.IDS_DOC=D.IDS AND
D.DATE_OP >= 8353 AND D.DATE_OP <= 9983 ORDER BY S.IDS_NUM,S.PART,S.OP

Hmm, seems like most of the cycles are going into hash joins. What
explain result do you get if you do "set enable_hashjoin to off"?

regards, tom lane

Hi Tom,
The result is slower with "set enable_hashjoin to off".

explain analyze select
S.IDS_NUM,S.OP,S.KOL,S.OTN_MED,S.CENA,S.DDS,S.KURS,S.TOT,S.DTO,S.PTO,S.DTON,MED.MNAME AS
MEDNAME,N.MNAME AS NOMENNAME,N.NUM AS NNUM,S.PART,S.IZV from A_DOC D left outer join
A_KLIENTI KL ON(D.IDS_KO=KL.IDS) , A_SKLAD S left outer join A_NOMEN N ON(S.IDS_NUM=N.IDS)
left outer join A_MED MED ON (N.OSN_MED=MED.IDS ) WHERE S.FID=0 AND S.IDS_DOC=D.IDS
AND D.DATE_OP >= 8353 AND D.DATE_OP <= 9983 ORDER BY S.IDS_NUM,S.PART,S.OP ;
NOTICE: QUERY PLAN:

Sort (cost=32422.62..32422.62 rows=66390 width=236) (actual time=16915.87..16960.48
rows=66095 loops=1)
-> Merge Join (cost=26076.60..27105.18 rows=66390 width=236) (actual
time=12328.92..13698.85 rows=66095 loops=1)
-> Sort (cost=2813.55..2813.55 rows=11813 width=34) (actual time=1037.42..1046.47
rows=12016 loops=1)
-> Merge Join (cost=1825.97..2014.52 rows=11813 width=34) (actual
time=536.28..716.99 rows=12016 loops=1)
-> Sort (cost=1365.64..1365.64 rows=11813 width=23) (actual
time=446.55..455.46 rows=12016 loops=1)
-> Seq Scan on a_doc d (cost=0.00..566.60 rows=11813 width=23)
(actual time=0.11..92.87 rows=12016 loops=1)
-> Sort (cost=460.33..460.33 rows=4542 width=11) (actual
time=89.24..99.64 rows=14126 loops=1)
-> Seq Scan on a_klienti kl (cost=0.00..184.42 rows=4542
width=11) (actual time=0.10..21.99 rows=4542 loops=1)
-> Sort (cost=23263.05..23263.05 rows=67666 width=202) (actual
time=11291.41..11344.76 rows=67666 loops=1)
-> Merge Join (cost=16812.87..17834.16 rows=67666 width=202) (actual
time=6861.56..8021.18 rows=67666 loops=1)
-> Index Scan using a_med_pkey on a_med med (cost=0.00..5.88 rows=167
width=16) (actual time=0.24..0.77 rows=40 loops=1)
-> Sort (cost=16812.87..16812.87 rows=67666 width=186) (actual
time=6861.25..6922.20 rows=67666 loops=1)
-> Merge Join (cost=9507.72..11383.98 rows=67666 width=186)
(actual time=3913.17..5229.99 rows=67666 loops=1)
-> Index Scan using a_nomen_pkey on a_nomen n
(cost=0.00..844.51 rows=6703 width=78) (actual time=0.29..66.41 rows=4600 loops=1)
-> Sort (cost=9507.72..9507.72 rows=67666 width=108)
(actual time=3909.80..3961.99 rows=67666 loops=1)
-> Seq Scan on a_sklad s (cost=0.00..4078.82
rows=67666 width=108) (actual time=0.14..1297.48 rows=67666 loops=1)
Total runtime: 17084.20 msec

EXPLAIN

I think that the problem is in sorting.
I make a test by reading the result of 66K rows ( in java) and sortet it ( on the same
station). I got it working in about 1 sec!
Any Idea how to improve the performance?

I will import 600K rows and will try the test and the comparation, but for the moment it
looks bad.
Can I fine tune the OS ?

The tables are :

CREATE TABLE "a_doc" (
"ids" varchar(20) NOT NULL,
"fid" int4 NOT NULL,
"ids_users" varchar(20) NOT NULL,
"nomer" varchar(20) NOT NULL,
"pofact" varchar(20),
"op" int4 NOT NULL,
"date_op" int4 NOT NULL,
"date_v" int4 NOT NULL,
"srok" int4,
"pla_type" int4 NOT NULL,
"ids_sklad" varchar(20) NOT NULL,
"state" int4,
"suma" float8,
"sumadds" float8,
"ids_ko" varchar(20),
"ko_name" varchar(100),
"ko_dn" varchar(20),
"ko_bulstat" varchar(20),
"ko_mol" varchar(100),
"ko_stav" varchar(100),
"ko_otgov" varchar(100),
"ko_adres" varchar(100),
"vid" int4,
"izgotvil" varchar(60),
"predal" varchar(60),
"proveril" varchar(60),
"time_ins" float8,
"time_prov" float8,
"last_change" int4,
"nie_name" varchar(60),
"nie_dn" varchar(20),
"nie_bulstat" varchar(20),
"nie_mol" varchar(60),
"nie_stav" varchar(60),
"nie_otgov" varchar(60),
"nie_adres" varchar(80),
"ko_grad" varchar(60),
"zab" varchar(255),
"vsd" int4,
"dogovor" varchar(50),
"veriga" int4,
"otndob" varchar(50),
"offic" int4,
"date_izl" int4,
"izl" int4,
"ids_mita" varchar(20),
"ids_transport" varchar(20),
CONSTRAINT "a_doc_pkey" PRIMARY KEY ("ids"),
CONSTRAINT "<unnamed>" FOREIGN KEY ("ids_users") REFERENCES "a_slujiteli" ("ids") ON
DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "<unnamed>" FOREIGN KEY ("pofact") REFERENCES "a_doc" ("ids") ON DELETE NO
ACTION ON UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "<unnamed>" FOREIGN KEY ("ids_ko") REFERENCES "a_klienti" ("ids") ON DELETE NO
ACTION ON UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "<unnamed>" FOREIGN KEY ("ids_sklad") REFERENCES "a_location" ("ids") ON
DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "<unnamed>" FOREIGN KEY ("ids_mita") REFERENCES "a_mita" ("ids") ON DELETE NO
ACTION ON UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "<unnamed>" FOREIGN KEY ("ids_transport") REFERENCES "a_transport" ("ids") ON
DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
) WITH OIDS;

CREATE TABLE "a_klienti" (
"ids" varchar(20) NOT NULL,
"fid" int4 NOT NULL,
"ids_klient_type" varchar(20),
"ids_person" varchar(20),
"ids_grad" varchar(20),
"nomer" varchar(20),
"mname" varchar(60) NOT NULL,
"mname_1" varchar(60),
"mname_2" varchar(60),
"dn" varchar(20),
"bulstat" varchar(20),
"mol" varchar(100),
"acc_nomer" varchar(20),
"vid" int4,
"klient" int4,
"dostav" int4,
"limit_dni" float8,
"limit_sum" float8,
"limit_sum_val" int4,
"licens" varchar(40),
"razreshitelno" varchar(40),
"otstapka" float8,
"bank_smetka" varchar(20),
"pla_method" int4,
"adres" varchar(80),
"isactive" int4,
"glaven" int4,
"ids_grupa" varchar(20),
"otgikop" varchar(100),
"denp" int4,
"m_kod" varchar(10),
"m_grad" varchar(60),
"m_adr" varchar(80),
"m_lice" varchar(100),
"nashnomer" varchar(30),
"adr_dost" varchar(200),
"m_tel" varchar(40),
"m_tel1" varchar(40),
"m_fax" varchar(40),
"m_mail" varchar(40),
"m_poluchil" varchar(80),
"m_p_egn" varchar(20),
"m_p_pass" varchar(80),
"old_name" varchar(60),
CONSTRAINT "a_klienti_pkey" PRIMARY KEY ("ids"),
CONSTRAINT "<unnamed>" FOREIGN KEY ("ids_klient_type") REFERENCES "a_klient_type" ("ids")
ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "<unnamed>" FOREIGN KEY ("ids_grupa") REFERENCES "a_kl_grupa" ("ids") ON
DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "<unnamed>" FOREIGN KEY ("ids_person") REFERENCES "a_slujiteli" ("ids") ON
DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMME

CREATE TABLE "a_sklad" (
"ids" varchar(20) NOT NULL,
"fid" int4 NOT NULL,
"ids_doc" varchar(20) NOT NULL,
"ids_num" varchar(20) NOT NULL,
"ids_slu" varchar(20) NOT NULL,
"ids_sklad" varchar(20) NOT NULL,
"op" int4 NOT NULL,
"zapr" int4 NOT NULL,
"nomnum" varchar(20) NOT NULL,
"nomname" varchar(60) NOT NULL,
"part" varchar(20) NOT NULL,
"seriennum" varchar(20),
"izv" int4 NOT NULL,
"kol" float8 NOT NULL,
"ids_med" int4 NOT NULL,
"med" varchar(20),
"ids_med_main" int4 NOT NULL,
"med_main" varchar(20),
"otn_med" float8,
"cena" float8,
"val" int4,
"kurs" float8,
"cenadds" float8,
"dds" float8,
"cena_lv" float8,
"cena_lvdds" float8,
"tot" float8,
"dto" float8,
"order_num" int4,
"ids_doc2" varchar(20),
"pto" float8,
"dton" float8,
"offic" int4,
"date_izl" int4,
"izl" int4,
"otch_cena" float8,
"cenamitalv" float8,
"cenataksilv" float8,
"cenatranslv" float8,
"cenazastrlv" float8,
CONSTRAINT "a_sklad_pkey" PRIMARY KEY ("ids"),
CONSTRAINT "OTN_MED" CHECK ((otn_med > 0)),
CONSTRAINT "<unnamed>" FOREIGN KEY ("ids_doc") REFERENCES "a_doc" ("ids") ON DELETE NO
ACTION ON UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "<unnamed>" FOREIGN KEY ("ids_num") REFERENCES "a_nomen" ("ids") ON DELETE NO
ACTION ON UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "<unnamed>" FOREIGN KEY ("ids_slu") REFERENCES "a_slujiteli" ("ids") ON DELETE
NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "<unnamed>" FOREIGN KEY ("ids_med") REFERENCES "a_med" ("ids") ON DELETE NO
ACTION ON UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "<unnamed>" FOREIGN KEY ("ids_med_main") REFERENCES "a_med" ("ids") ON DELETE
NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "<unnamed>" FOREIGN KEY ("ids_sklad") REFERENCES "a_location" ("ids") ON
DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
) WITH OIDS;

CREATE TABLE "a_nomen" (
"ids" varchar(20) NOT NULL,
"fid" int4 NOT NULL,
"ids_grupa" varchar(20),
"ids_accktgr" varchar(20),
"num" varchar(20),
"katalojen_num" varchar(20),
"mname" varchar(60),
"mname_1" varchar(60),
"mname_2" varchar(60),
"osn_med" int4,
"dop1_med" int4,
"otn_dop1_med" float8,
"dop2_med" int4,
"otn_dop2_med" float8,
"cena_edr" float8,
"cena_edr_val" int4,
"cena_dreb" float8,
"cena_dreb_val" int4,
"cena_dost" float8,
"cena_dost_val" int4,
"minnal" float8,
"sert" varchar(40),
"part" varchar(20),
"dds" float8,
"mitnnum" varchar(20),
"accnum" varchar(20),
"kasa_num" varchar(20),
"kasa_name" varchar(40),
"adres" varchar(60),
"activ" int4,
"barkod" varchar(20),
"maxto" float8,
"to_dist_edro" float8,
"to_dist_dreb" float8,
"zabelejka" varchar(100),
"teglo_br" float8,
"teglo_neto" float8,
"abc" varchar(15),
"cena_3" float8,
"cena_3_val" int4,
"cena_4" float8,
"cena_4_val" int4,
"cena_5" float8,
"cena_5_val" int4,
"cena_6" float8,
"cena_6_val" int4,
"cena_7" float8,
"cena_7_val" int4,
"cena_8" float8,
"cena_8_val" int4,
"minnal_centr" float8,
"time_dost" float8,
"cena_fakt" float8,
"cena_fakt_val" int4,
"zapas" float8,
"old_num" varchar(20),
CONSTRAINT "a_nomen_num_key" UNIQUE ("num"),
CONSTRAINT "a_nomen_pkey" PRIMARY KEY ("ids"),
CONSTRAINT "<unnamed>" FOREIGN KEY ("ids_grupa") REFERENCES "a_nom_gr" ("ids") ON DELETE
NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "<unnamed>" FOREIGN KEY ("ids_accktgr") REFERENCES "a_acc_nom" ("ids") ON
DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "<unnamed>" FOREIGN KEY ("osn_med") REFERENCES "a_med" ("ids") ON DELETE NO
ACTION ON UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "<unnamed>" FOREIGN KEY ("dop1_med") REFERENCES "a_med" ("ids") ON DELETE NO
ACTION ON UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "<unnamed>" FOREIGN KEY ("dop2_med") REFERENCES "a_med" ("ids") ON DELETE NO
ACTION ON UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
) WITH OIDS;

CREATE TABLE "a_med" (
"ids" int4 NOT NULL,
"fid" int4,
"mname" varchar(20),
"mname_1" varchar(20),
"mname_2" varchar(20),
"order_num" int4,
CONSTRAINT "a_med_mname_key" UNIQUE ("mname", "fid"),
CONSTRAINT "a_med_pkey" PRIMARY KEY ("ids")
) WITH OIDS;

The sizes:

a_klienti - 4542 rows.
a_nomen - 6703 rows.
a_med - 167 rows.
a_doc - 12040 rows.
a_sklad - 67666 rows.

In the production system we need the sizes x10 !

Any ideas?

regards,
Ivan

Hi Tom,

I hope !
I changed it to 64 MB .

At the momenth I have results with 215 000 records.
The times is 28 sec ( for 66 000 it was ~ 8.5 sec.).

What is the normal perfornace for pg ?

And can you send me exampel conf file for better performance ?

regards,
Ivan.

Tom Lane wrote:

pginfo <pginfo@t1.unisoftbg.com> writes:

I think that the problem is in sorting.

Not according to the EXPLAIN results ... but did you change sort_mem?
The default value is kinda small.

regards, tom lane