Re: [SQL] Questions about vacuum analyze

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