X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 14835D1D5E4 for ; Tue, 17 Feb 2004 23:55:46 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 02117-02 for ; Tue, 17 Feb 2004 19:55:47 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 12B69D1D59C for ; Tue, 17 Feb 2004 19:55:44 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i1HNtheY019024; Tue, 17 Feb 2004 18:55:43 -0500 (EST) To: "Todd Fulton" Cc: pgsql-performance@postgresql.org Subject: Re: long running query running too long In-reply-to: <001301c3f578$7649bb10$326aa8c0@juarez> References: <001301c3f578$7649bb10$326aa8c0@juarez> Comments: In-reply-to "Todd Fulton" message dated "Tue, 17 Feb 2004 09:06:48 -0800" Date: Tue, 17 Feb 2004 18:55:42 -0500 Message-ID: <19023.1077062142@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/201 X-Sequence-Number: 5725 "Todd Fulton" 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