Re: Sort causes system to freeze

Craig James wrote:
> Maybe this is an obviously dumb thing to do,

... and it was. I answered my own question: The problem came from using psql(1) to do something I should have done with pg_dump.

> 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

Craig