Skip site navigation (1) Skip section navigation (2)

Re: Sort time

From: pginfo <pginfo(at)t1(dot)unisoftbg(dot)com>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>,"pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Sort time
Date: 2002-11-15 16:22:28
Message-ID: 3DD51F44.B4A68B50@t1.unisoftbg.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi,
Yes I have indexes on all this fields.
Also I vacuumed and that is the result after it.
Actualy I do not see what bad in query execution. The problem is in sort
time!

regards.

Josh Berkus wrote:

> Pginfo,
>
> > 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
>
> Pardon me if we've been over this ground, but that's a *lot* of seq
> scans for this query.   It seems odd that there's not *one* index scan.
>
> Have you tried indexing *all* of the following fields?
> S.FID
> N.OSN_MED
> S.IDS_NUM
> N.IDS
> S.IDS_DOC
> D.IDS
> (check to avoid duplicate indexes.  don't forget to VACUUM ANALYZE
> after you index)
>
> -Josh Berkus




In response to

Responses

pgsql-performance by date

Next:From: Josh BerkusDate: 2002-11-15 17:12:55
Subject: Re: Sort time
Previous:From: Tom LaneDate: 2002-11-15 15:59:17
Subject: Re: Sort time

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group