Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 0562764FD33 for ; Tue, 2 Dec 2008 02:59:18 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 65091-01 for ; Tue, 2 Dec 2008 02:59:15 -0400 (AST) X-Greylist: delayed 01:09:55.705194 by SQLgrey-1.7.6 Received: from relay-2.mail.nethere.net (relay-2.mail.nethere.net [66.63.128.162]) by mail.postgresql.org (Postfix) with ESMTP id DE45A64FD27 for ; Tue, 2 Dec 2008 02:59:14 -0400 (AST) Received: from scan-1.mail.nethere.net (scan-1.mail.nethere.net [66.63.128.132]) by relay-2.mail.nethere.net (Postfix) with ESMTP id A305712B480 for ; Mon, 1 Dec 2008 21:49:16 -0800 (PST) Received: from mta-2.mail.nethere.net by scan-1.mail.nethere.net with LMTP id pRhxdbBCcEKw; Mon, 1 Dec 2008 21:49:16 -0800 (PST) Received: from lisa-james-computer.local (wsip-68-224-174-233.sd.sd.cox.net [68.224.174.233]) by mta-2.mail.nethere.net (Postfix) with ESMTP id 859EC130C20 for ; Mon, 1 Dec 2008 21:49:16 -0800 (PST) Message-ID: <4934CC58.1050309@emolecules.com> Date: Mon, 01 Dec 2008 21:49:12 -0800 From: Craig James User-Agent: Thunderbird 2.0.0.18 (Macintosh/20081105) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Sort causes system to freeze References: <8763maouy9.fsf@oxford.xeocode.com> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200812/2 X-Sequence-Number: 32038 Maybe this is an obviously dumb thing to do, but it looked reasonable to me. The problem is, the seemingly simple sort below causes a fairly powerful computer to completely freeze for 5-10 minutes. During the sort, you can't login, you can't use any shell sessions you already have open, the Apache server barely works, and even if you do "nice -20 top" before you start the sort, the top(1) command comes to a halt while the sort is proceeding! As nearly as I can tell, the sort operation is causing a swap storm of some sort -- nothing else in my many years of UNIX/Linux experience can cause a "nice -20" process to freeze. The sort operation never finishes -- it's always killed by the system. Once it dies, everything returns to normal. This is 8.3.0. (Yes, I'll upgrade soon.) Is this a known bug, or do I have to rewrite this query somehow? Maybe add indexes to all four columns being sorted? Thanks! Craig => explain select * from plus order by supplier_id, compound_id, units, price; QUERY PLAN ----------------------------------------------------------------------- Sort (cost=5517200.48..5587870.73 rows=28268100 width=65) Sort Key: supplier_id, compound_id, units, price -> Seq Scan on plus (cost=0.00..859211.00 rows=28268100 width=65) => \d plus Table "emol_warehouse_1.plus" Column | Type | Modifiers ---------------+---------------+----------- supplier_id | integer | supplier_name | text | compound_id | text | amount | text | units | text | price | numeric(12,2) | currency | text | description | text | sku | text | Indexes: "i_plus_compound_id" btree (supplier_id, compound_id) "i_plus_supplier_id" btree (supplier_id) max_connections = 1000 shared_buffers = 2000MB work_mem = 256MB max_fsm_pages = 1000000 max_fsm_relations = 5000 synchronous_commit = off #wal_sync_method = fdatasync wal_buffers = 256kB checkpoint_segments = 30 effective_cache_size = 4GB Machine: Dell, 8x64-bit CPUs, 8GB ram, Perc6i battery-backed RAID controller, 8 disks as RAID10