Обсуждение: Query performance with small data base

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:

Sort  (cost=14730.62..14730.62 rows=66390 width=236) (actual
time=8544.01..8588.09 rows=66095 loops=1)
  ->  Hash Join  (cost=1459.51..9413.19 rows=66390 width=236) (actual
time=341.04..5225.99 rows=66095 loops=1)
        ->  Hash Join  (cost=460.88..7077.17 rows=67666 width=202)
(actual time=70.67..3702.48 rows=67666 loops=1)
              ->  Hash Join  (cost=456.79..5719.77 rows=67666 width=186)
(actual time=68.62..2650.36 rows=67666 loops=1)
                    ->  Seq Scan on a_sklad s  (cost=0.00..4078.82
rows=67666 width=108) (actual time=0.17..1349.74 rows=67666 loops=1)
                    ->  Hash  (cost=440.03..440.03 rows=6703 width=78)
(actual time=68.01..68.01 rows=0 loops=1)
                          ->  Seq Scan on a_nomen n  (cost=0.00..440.03
rows=6703 width=78) (actual time=0.19..54.32 rows=6703 loops=1)
              ->  Hash  (cost=3.67..3.67 rows=167 width=16) (actual
time=0.98..0.98 rows=0 loops=1)
                    ->  Seq Scan on a_med med  (cost=0.00..3.67 rows=167
width=16) (actual time=0.21..0.69 rows=167 loops=1)
        ->  Hash  (cost=969.10..969.10 rows=11813 width=34) (actual
time=269.81..269.81 rows=0 loops=1)
              ->  Hash Join  (cost=195.77..969.10 rows=11813 width=34)
(actual time=30.41..247.55 rows=12016 loops=1)
                    ->  Seq Scan on a_doc d  (cost=0.00..566.60
rows=11813 width=23) (actual time=0.17..92.28 rows=12016 loops=1)
                    ->  Hash  (cost=184.42..184.42 rows=4542 width=11)
(actual time=29.07..29.07 rows=0 loops=1)
                          ->  Seq Scan on a_klienti kl
(cost=0.00..184.42 rows=4542 width=11) (actual time=0.23..21.03
rows=4542 loops=1)
Total runtime: 8649.46 msec


I have made vacuum analyze.

I have indexes on all the references usen in query and all x.IDS are
varchar(20).

I tested exact the same data on oracle and got it running in 1.5 sec. !

Can I fine tune the server or db to have better result on postgres or it
is normal ?

I the real db we will have in result 600 - 700 000 rows.

Many thanks,
Ivan.

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

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

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