Re: [HACKERS] sorting big tables :(

> > I have an idea.  Can he run CLUSTER on the data?  If so, the sort will
> > not use small batches, and the disk space during sort will be reduced.
> > However, I think CLUSTER will NEVER finish on such a file, unless it is
> > already pretty well sorted.
>
>     Okay...then we *do* have a table size limit problem?  Tables that
> just get too large to be manageable?  Maybe this is one area we should be
> looking at as far as performance is concerned?

Well, cluster moves one row at a time, so if the table is very
fragmented, the code is slow because it is seeking all over the table.
See the cluster manual pages for an alternate solution, the uses ORDER
BY.


>
>     One thing that just pop'd to mind, concerning the above CLUSTER
> command...what would it take to have *auto-cluster'ng*?  Maybe provide a
> means of marking a field in a table for this purpose?

Hard to do.  That's what we have indexes for.

>
>     One of the things that the Unix FS does is auto-defragmenting, at
> least the UFS one does.  Whenever the system is idle (from my
> understanding), the kernel uses that time to clean up the file systems, to
> reduce the file system fragmentation.
>
>     This is by no means SQL92, but it would be a neat
> "extension"...let me specify a "CLUSTER on" field.  Then, as I'm entering
> data into the database, periodically check for fragmentation of the data
> and clean up accordingly.  If done by the system, reasonably often, it
> shouldn't take up *too* much time, as most of the data should already be
> in order...
>
>     That would have the side-benefit of speeding up the "ORDER by" on
> that field also...

We actually can have a CLUSTER ALL command, that does this.  No one has
implemented it yet.

--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)