Re: Query performance with small data base - Mailing list pgsql-general

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