From: Tom Lane <tgl@sss.pgh.pa.us>
To: Steven M. Wheeler <swheeler@sabre.com>
To: pgsql-sql@hub.org
Subject: Re: [SQL] Questions about vacuum analyze
Date: Sun, 10 Oct 1999 21:50:34 -0400
Message-ID: <3631.939606634@sss.pgh.pa.us> (raw)
In-Reply-To: <3104.939393781@sss.pgh.pa.us>
I wrote:
> "Steven M. Wheeler" <swheeler@sabre.com> writes:
>> I am now running into another problem. I need to do select distinct from the
>> currnt to get dates for maintenance activity (rows to summarize and move to
>> history). When ever I do I get this message in my server.log file:
>>
>> NOTICE: BufFileRead: should have flushed after writing
>>
>> And I get 0 records selected.
> After some code browsing I have come up with a theory: is it possible
> that you are running out of disk space during the sort? I see psort.c
> neglects to check for write failure :-(, although I am not entirely
> clear on why that particular message and nothing else would come out
> after it bulled ahead with an incomplete temporary file.
> Sorting a large table seems to require temp disk space equal to several
> times the size of the table. Since you said your table is 40M rows,
> I can easily believe you don't have that much free space...
A further thought: 40M rows could well be more than 4gig of data.
Although Postgres supports tables that large (by segmenting them into
1gig-apiece files to avoid OS limitations on file size), we currently
do not cope with temporary sort files that exceed 4 gig, which means
a "select distinct" on that much data will fail regardless of whether
you have enough free disk space :-(. We have a TODO item to fix this.
After looking at psort.c, I see a bunch of other shortcomings, one being
that it's using a polyphase merge algorithm --- which was hot stuff back
when people did this sort of thing on magnetic tape drives, but it's
less than appropriate for sorting on disk. The worst problem with it is
that the space consumption is about 4x the actual data volume, which is
not too cool when you're talking about a huge file to begin with...
Meanwhile, there is a completely separate implementation of external
sorting over in nbtsort.c (where it's only used for CREATE INDEX,
AFAICS), with a somewhat different set of deficiencies.
I think I'll add "rewrite sort code from the ground up" to my to-do
list ;-)
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, swheeler@sabre.com, pgsql-sql@hub.org
Subject: Re: [SQL] Questions about vacuum analyze
In-Reply-To: <3631.939606634@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox