Re: Sort time - Mailing list pgsql-performance

Sorry,
I can post a little more info:

I run the same query ( and receive the same result), but in this time I
started vmstat 2, to see the system state.
The results:

gibi=# 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,D.DATE_OP  from
A_DOC D  , A_SKLAD S,  A_NOMEN N ,A_MED MED       WHERE S.FID=0 AND
N.OSN_MED=MED.ID
S AND S.IDS_NUM=N.IDS AND S.IDS_DOC=D.IDS  ORDER BY  S.IDS_NUM,S.PART,S.OP ;
NOTICE:  QUERY PLAN:

Sort  (cost=100922.53..100922.53 rows=22330 width=215) (actual
time=109786.23..110231.74 rows=679743 loops=1)
  ->  Hash Join  (cost=9153.28..99309.52 rows=22330 width=215) (actual
time=12572.01..56330.28 rows=679743 loops=1)
        ->  Hash Join  (cost=2271.05..91995.05 rows=30620 width=198) (actual
time=7082.66..36482.57 rows=679743 loops=1)
              ->  Seq Scan on a_sklad s  (cost=0.00..84181.91 rows=687913
width=111) (actual time=6812.81..23085.36 rows=679743 loops=1)
              ->  Hash  (cost=2256.59..2256.59 rows=5784 width=87) (actual
time=268.05..268.05 rows=0 loops=1)
                    ->  Hash Join  (cost=2.52..2256.59 rows=5784 width=87)
(actual time=125.25..255.48 rows=5784 loops=1)
                          ->  Seq Scan on a_nomen n  (cost=0.00..2152.84
rows=5784 width=74) (actual time=120.63..216.93 rows=5784 loops=1)
                          ->  Hash  (cost=2.42..2.42 rows=42 width=13)
(actual time=0.57..0.57 rows=0 loops=1)
                                ->  Seq Scan on a_med med  (cost=0.00..2.42
rows=42 width=13) (actual time=0.24..0.46 rows=42 loops=1)
        ->  Hash  (cost=6605.19..6605.19 rows=110819 width=17) (actual
time=5485.90..5485.90 rows=0 loops=1)
              ->  Seq Scan on a_doc d  (cost=0.00..6605.19 rows=110819
width=17) (actual time=61.18..5282.99 rows=109788 loops=1)
Total runtime: 110856.36 msec

EXPLAIN

 vmstat 2
   procs                      memory    swap          io     system
cpu
 r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs  us  sy
id
 0  0  0  32104 196932  77404 948256   0   0    30    12   24    12   6   1
27
 0  1  1  32104 181792  77404 952416   0   0  2080    36  328   917   7   9
84
 0  1  0  32104 170392  77404 959584   0   0  3584    16  533  1271   5   4
91
 1  0  0  32104 162612  77404 965216   0   0  2816     0  514  1332   2   6
92
 1  0  0  32104 146832  77404 979956   0   0  7370    18  631  1741   5  16
79
 1  0  0  32104 129452  77404 997364   0   0  8704     0  719  1988   7   7
86
 0  2  1  32104 116016  77404 1010632   0   0  6634     8  563  1495   6  20
74
 1  0  0  32104 109844  77404 1013360   0   0  1364     2  228   584  31  24
45
 1  0  0  32104 101244  77404 1013364   0   0     2     0  103   219  43  11
46
 1  0  0  32104  84652  77404 1021328   0   0  3982    16  402   455  44   8
49
 3  0  0  32104  72916  77404 1024404   0   0  1538     0  294   215  44   5
51
 2  0  0  32104  63844  77404 1024404   0   0     0    10  103   222  47   3
50
 1  0  0  32104  54600  77404 1024404   0   0     0     0  102   222  55   6
39
 1  0  0  32104  45472  77404 1024404   0   0     0     0  102   220  45   6
50
 1  0  0  32104  36060  77404 1024404   0   0     0    10  103   215  45   5
50
 2  0  0  32104  26640  77404 1024404   0   0     0     0  106   218  43   7
50
 2  0  0  32104  17440  77404 1024404   0   0     0    10  148   253  46   6
48
 1  0  0  32104  10600  77404 1022004   0   0     0     0  102   215  42   8
50
 1  0  0  32104  10604  77404 1013900   0   0     0     0  103   212  41   9
50
 1  0  0  32104  10600  77404 1006452   0   0     0    26  106   225  38  12
50
 2  0  0  32104  10600  77404 997412   0   0     0     0  102   213  48   3
50
   procs                      memory    swap          io     system
cpu
 r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs  us  sy
id
 1  0  0  32104  10572  77428 988936   0   0   340   118  214   455  62   8
29
 1  0  0  32104  10532  77432 979872   0   0   642   124  307   448  70  12
18
 1  0  0  32104  10516  77432 970316   0   0     0     0  102   238  49   6
45
 1  0  0  32104  10508  77432 960880   0   0     0    46  105   224  50   5
45
 1  0  0  32104  10500  77432 951740   0   0  3398    34  174   445  47   9
44
 1  0  1  32104  10112  77432 943588   0   0  8192    94  289   544  50  12
39
 1  0  0  32104  10484  77432 937204   0   0 16896     0  386  1698  37  20
43
 2  0  0  32104  10484  77432 930004   0   0 14080     0  345  1415  39  17
45
 3  0  0  32104  27976  77432 925592   0   0  1844    16  136   329  46   6
49
 2  0  0  32104  27924  77432 925592   0   0     0     0  104   220  50   0
49
 2  0  0  32104  27756  77436 925592   0   0     0     8  103   222  51   2
47
 1  0  0  32104  27756  77436 925592   0   0     0     0  102   222  54   1
45
 1  0  0  32104  27756  77436 925592   0   0     0     0  102   220  55   0
45
 1  0  0  32104  27424  77436 925592   0   0     0    24  104   224  54   1
45
 1  0  0  32104  27424  77436 925592   0   0     0     0  102   218  55   0
45
 3  0  0  32104  27424  77436 925592   0   0     0     8  103   221  55   0
45
 1  0  0  32104  27424  77436 925592   0   0     0     0  103   222  55   0
45
 1  0  0  32104  27456  77436 925592   0   0     0     0  104   222  55   0
45
 1  0  0  32104  27456  77436 925592   0   0     0     8  104   222  55   0
45
 2  0  0  32104  26792  77436 925592   0   0     0     0  102   218  55   1
44
 2  0  0  32104  26792  77436 925592   0   0     0     8  103   222  55   0
44
   procs                      memory    swap          io     system
cpu
 r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs  us  sy
id
 2  0  0  32104  26792  77436 925592   0   0     0     0  102   221  66   0
33
 1  0  0  32104  26792  77436 925592   0   0     0     0  103   221  55   0
44
 1  0  0  32104  26792  77436 925592   0   0     0     8  103   219  55   0
44
 1  0  0  32104  26792  77436 925592   0   0     0     0  104   221  56   0
44
 2  0  0  32104  26792  77436 925592   0   0     0     8  105   223  56   0
44
 1  0  0  32104  26792  77436 925592   0   0     0     0  102   222  56   0
44
 1  0  0  32104  26792  77436 925592   0   0     0     8  106   223  55   1
44
 1  0  0  32104  26792  77436 925592   0   0     0     0  102   216  56   0
44
 2  0  0  32104  26792  77436 925592   0   0     0     0  102   221  56   0
43
 2  0  0  32104  26628  77436 925592   0   0     0    26  106   230  57   0
43
 1  0  0  32104  26768  77440 925592   0   0     0    12  104   228  57   0
43
 1  0  0  32104  26760  77448 925592   0   0     0    30  106   226  56   1
43
 2  0  0  32104  26168  77448 925592   0   0     0     0  102   221  57   0
43
 1  0  0  32104  28088  77448 925592   0   0     0     0  103   220  46  12
42

Can I tune better my linux box or pq to get faster execution?

regards.



scott.marlowe wrote:

> On Thu, 14 Nov 2002, pginfo wrote:
>
> > Hi,
> >
> > Why is the sort part of my query getting so much time?
> >
> > I run a relative complex query and it gets about 50 sec.
> > For sorting I need another 50 sec!
> >
> > Can I increase the sort memory for better performance?
> > How meny memory is needet for the sort in pg.
> > The same data readet in java and sorted cost 10 sec !
>
> Increasing sort_mem can help, but often the problem is that your query
> isn't optimal.  If you'd like to post the explain analyze output of your
> query, someone might have a hint on how to increase the efficiency of the
> query.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html