Re: Sort time

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