Re: indexing words slow

> I have to conclude that because of the way this table is created by
> slicing words, its layout is almost random. The table is 272MB, and
> with 8k/page, that is 34,000 pages. If we are going for 2,500 rows,
> odds are that each row is in a separate page. So, to do the query, we
> are retrieving 2,500 8k pages, or 20MB of random data on the disk. How
> long does it take to issue 2,500 disk requests that are scattered all
> over the disk. Probably 11-22 seconds.

> My OS only lets me have 400 8k buffers, or 3.2MB of buffer. As we are
> transferring 2,500 8k pages or 20MB of data, it is no surprise that the
> buffer cache doesn't help much. Sometime, the data is grouped together
> on the disk, and that is why some are fast, but often they are not, and
> certainly in a situation where you are looking for two words to appear
> on the same row, they certainly are not on adjacent pages.

Thanx bruce, this si a good report :) This confirms what I thought myself
too btw.

> Just started running CLUSTER, and it appears to be taking forever. Does
> not seem to be using the disk, but a lot of CPU. It appears to be
> caught in an endless loop.

Note that cluster actually *did* something on my system. It created a
temp-table, which was populated, but very very slowly. I also had
disk-activity, but maybe you don't notice much of it because of your SCSI
disks....

Maarten

_____________________________________________________________________________
| TU Delft, The Netherlands, Faculty of Information Technology and Systems |
| Department of Electrical Engineering |
| Computer Architecture and Digital Technique section |
| M(dot)Boekhold(at)et(dot)tudelft(dot)nl |
-----------------------------------------------------------------------------