sort on huge table

From: Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)jp>
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: sort on huge table
Date: 1999-10-13 10:03:20
Message-ID: 199910131003.TAA06423@srapc451.sra.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I came across problems with sorting a huge (2.4GB) table.

o it took 46 minutes to complete following query:

select * from test2 order by i desc limit 100;

to get 0 results.

i|t
-+-
(0 rows)

I assume this is a failure.

note: this is Pentium III x2 with 512MB RAM running RedHat Linux 6.0.

o I got NOTICE: BufFileRead: should have flushed after writing
at the very end of the processing.

o it produced 7 sort temp files each having size of 1.4GB (total 10GB)

Here is the table I used for testing(no index):

CREATE TABLE test2 (
i int4,
t text);

This has 10000000 records and the table file sizes are:

$ ls -ls test2*
1049604 -rw------- 1 postgres postgres 1073741824 Oct 4 18:32 test2
1049604 -rw------- 1 postgres postgres 1073741824 Oct 5 01:19 test2.1
327420 -rw------- 1 postgres postgres 334946304 Oct 13 17:40 test2.2

--
Tatsuo Ishii

Browse pgsql-hackers by date

  From Date Subject
Next Message Hiroshi Inoue 1999-10-13 10:14:47 RE: Scan by TID (was RE: [HACKERS] How to add a new build-in operator)
Previous Message Chairudin Sentosa Harjo 1999-10-13 10:01:42 Re: [HACKERS] Updated book outline