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

Re: long running query running too long

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Todd Fulton" <pongo(at)jah(dot)net>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: long running query running too long
Date: 2004-02-17 23:55:42
Message-ID: 19023.1077062142@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
"Todd Fulton" <pongo(at)jah(dot)net> writes:
> prod=# explain analyze SELECT t.tgpid, t.directoryname, t.templateid,
> count(*) AS requested FROM (spk_tgp t JOIN spk_tgplog l ON ((t.tgpid =
> l.tgpid))) GROUP BY t.tgpid, t.directoryname, t.templateid;
> NOTICE:  QUERY PLAN:
 
> Aggregate  (cost=2740451.66..2820969.41 rows=805178 width=48) (actual
> time=460577.85..528968.17 rows=1875 loops=1)
>   ->  Group  (cost=2740451.66..2800839.97 rows=8051775 width=48) (actual
> time=460577.57..516992.19 rows=8117748 loops=1)
>         ->  Sort  (cost=2740451.66..2740451.66 rows=8051775 width=48)
> (actual time=460577.55..474657.59 rows=8117748 loops=1)
>               ->  Hash Join  (cost=128.26..409517.83 rows=8051775
> width=48) (actual time=11.45..85332.88 rows=8117748 loops=1)
>                     ->  Seq Scan on spk_tgplog l  (cost=0.00..187965.75
> rows=8051775 width=8) (actual time=0.03..28926.67 rows=8125690 loops=1)
>                     ->  Hash  (cost=123.41..123.41 rows=1941 width=40)
> (actual time=11.28..11.28 rows=0 loops=1)
>                           ->  Seq Scan on spk_tgp t  (cost=0.00..123.41
> rows=1941 width=40) (actual time=0.06..7.60 rows=1880 loops=1)
> Total runtime: 529542.66 msec

The join itself is being done fine --- I doubt there is another option
that will go faster, given the difference in the table sizes.  Note the
join step completes in only 85 seconds.  What is killing you is the
sorting/grouping operation.  You could try increasing sort_mem to see
if that makes it go any faster, but I suspect the best answer would be to
update to PG 7.4.  7.4 will probably use hash aggregation for this and
avoid the sort altogether.

			regards, tom lane

In response to

pgsql-performance by date

Next:From: Christopher Kings-LynneDate: 2004-02-18 01:50:00
Subject: Re: Slow response of PostgreSQL
Previous:From: PC DrewDate: 2004-02-17 23:17:05
Subject: Re: long running query running too long

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