X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 19995D7AFA for ; Tue, 8 Nov 2005 18:06:10 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 35197-10 for ; Tue, 8 Nov 2005 22:06:07 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by svr1.postgresql.org (Postfix) with ESMTP id E4F59D6873 for ; Tue, 8 Nov 2005 18:06:07 -0400 (AST) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id B6BFC31059; Tue, 8 Nov 2005 23:06:08 +0100 (MET) From: Charlie Savage X-Newsgroups: pgsql.performance Subject: Re: Sort performance on large tables Date: Tue, 08 Nov 2005 15:06:04 -0700 Organization: Hub.Org Networking Services Lines: 82 Message-ID: References: <43704E1D.80708@interserv.com> <437088A1.8090304@archonet.com> Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Thunderbird 1.5 (Windows/20051025) In-Reply-To: <437088A1.8090304@archonet.com> To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200511/145 X-Sequence-Number: 15402 Thanks everyone for the feedback. I tried increasing work_mem: set work_mem to 300000; select tlid, min(ogc_fid) from completechain group by tld; The results are: "GroupAggregate (cost=9041602.80..10003036.88 rows=48071704 width=8) (actual time=4371749.523..5106162.256 rows=47599910 loops=1)" " -> Sort (cost=9041602.80..9161782.06 rows=48071704 width=8) (actual time=4371690.894..4758660.433 rows=48199165 loops=1)" " Sort Key: tlid" " -> Seq Scan on completechain (cost=0.00..2228584.04 rows=48071704 width=8) (actual time=49.518..805234.970 rows=48199165 loops=1)" "Total runtime: 5279988.127 ms" Thus the time decreased from 8486 seconds to 5279 seconds - which is a nice improvement. However, that still leaves postgresql about 9 times slower. I tried increasing work_mem up to 500000, but at that point the machine started using its swap partition and performance degraded back to the original values. Charlie Richard Huxton wrote: > Charlie Savage wrote: >> Hi everyone, >> >> I have a question about the performance of sort. > >> Note it takes over 10 times longer to do the sort than the full >> sequential scan. >> >> Should I expect results like this? I realize that the computer is >> quite low-end and is very IO bound for this query, but I'm still >> surprised that the sort operation takes so long. > > The sort will be spilling to disk, which will grind your I/O to a halt. > >> work_mem = 16384 # in Kb > > Try upping this. You should be able to issue "set work_mem = 100000" > before running your query IIRC. That should let PG do its sorting in > larger chunks. > > Also, if your most common access pattern is ordered via tlid look into > clustering the table on that. Richard Huxton wrote: > Charlie Savage wrote: >> Hi everyone, >> >> I have a question about the performance of sort. > >> Note it takes over 10 times longer to do the sort than the full >> sequential scan. >> >> Should I expect results like this? I realize that the computer is >> quite low-end and is very IO bound for this query, but I'm still >> surprised that the sort operation takes so long. > > The sort will be spilling to disk, which will grind your I/O to a halt. > >> work_mem = 16384 # in Kb > > Try upping this. You should be able to issue "set work_mem = 100000" > before running your query IIRC. That should let PG do its sorting in > larger chunks. > > Also, if your most common access pattern is ordered via tlid look into > clustering the table on that.