Re: Sort time - Mailing list pgsql-performance

Hi,
The sort mem is prety big at the moment.
For this tuning I use 256 MB for sort mem !

The explain plan is:
EXPLAIN
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=111241.88..111735.33 rows=679743 loops=1)
  ->  Hash Join  (cost=9153.28..99309.52 rows=22330 width=215) (actual
time=3386.45..53065.59 rows=679743 loops=1)
        ->  Hash Join  (cost=2271.05..91995.05 rows=30620 width=198) (actual
time=2395.76..36710.54 rows=679743 loops=1)
              ->  Seq Scan on a_sklad s  (cost=0.00..84181.91 rows=687913
width=111) (actual time=2111.30..22354.10 rows=679743 loops=1)
              ->  Hash  (cost=2256.59..2256.59 rows=5784 width=87) (actual
time=282.95..282.95 rows=0 loops=1)
                    ->  Hash Join  (cost=2.52..2256.59 rows=5784 width=87)
(actual time=132.54..270.29 rows=5784 loops=1)
                          ->  Seq Scan on a_nomen n  (cost=0.00..2152.84
rows=5784 width=74) (actual time=127.97..218.02 rows=5784 loops=1)
                          ->  Hash  (cost=2.42..2.42 rows=42 width=13)
(actual time=0.55..0.55 rows=0 loops=1)
                                ->  Seq Scan on a_med med  (cost=0.00..2.42
rows=42 width=13) (actual time=0.22..0.43 rows=42 loops=1)
        ->  Hash  (cost=6605.19..6605.19 rows=110819 width=17) (actual
time=987.26..987.26 rows=0 loops=1)
              ->  Seq Scan on a_doc d  (cost=0.00..6605.19 rows=110819
width=17) (actual time=67.96..771.54 rows=109788 loops=1)
Total runtime: 112402.30 msec

EXPLAIN

All IDS_XXX fields are varchar(20),S.PART is also varchar(20).
All tables are indexed.

Can I change any parameters on my pg to increase the speed.
It looks very slow.

Only for test ( I do not need it) I executed:
EXPLAIN
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.OP ;
NOTICE:  QUERY PLAN:

Sort  (cost=100922.53..100922.53 rows=22330 width=215) (actual
time=62141.60..62598.05 rows=679743 loops=1)
  ->  Hash Join  (cost=9153.28..99309.52 rows=22330 width=215) (actual
time=9032.59..54703.33 rows=679743 loops=1)
        ->  Hash Join  (cost=2271.05..91995.05 rows=30620 width=198) (actual
time=8046.91..39132.91 rows=679743 loops=1)
              ->  Seq Scan on a_sklad s  (cost=0.00..84181.91 rows=687913
width=111) (actual time=7790.01..25565.74 rows=679743 loops=1)
              ->  Hash  (cost=2256.59..2256.59 rows=5784 width=87) (actual
time=255.32..255.32 rows=0 loops=1)
                    ->  Hash Join  (cost=2.52..2256.59 rows=5784 width=87)
(actual time=123.40..243.02 rows=5784 loops=1)
                          ->  Seq Scan on a_nomen n  (cost=0.00..2152.84
rows=5784 width=74) (actual time=118.75..204.41 rows=5784 loops=1)
                          ->  Hash  (cost=2.42..2.42 rows=42 width=13)
(actual time=0.59..0.59 rows=0 loops=1)
                                ->  Seq Scan on a_med med  (cost=0.00..2.42
rows=42 width=13) (actual time=0.25..0.47 rows=42 loops=1)
        ->  Hash  (cost=6605.19..6605.19 rows=110819 width=17) (actual
time=982.22..982.22 rows=0 loops=1)
              ->  Seq Scan on a_doc d  (cost=0.00..6605.19 rows=110819
width=17) (actual time=73.46..787.87 rows=109788 loops=1)
Total runtime: 63194.60 msec

The field S.OP is INT.

It is huge improvement when I sort by INT field, but I need to sort varchar
fileds !

Is this normal for pg to work so slow with varchar or I can change the setup.

Also I think the query time ( without sorting is big).

regards and thanks in advance.

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