Re: indexing words slow - Mailing list pgsql-hackers

>
> On Wed, 11 Mar 1998, Bruce Momjian wrote:
>
> > >
> > > Hi,
> > >
> > > I have done a little more testing, and the performance bottleneck
> > > seems definitely be memory related. Note that it does not really seems
> > > to be dependend on buffer-settings, but really on disk caches.
> > >
> > > additional info:
> > >     the index on this table is around 155 Megs big
> > >
> > > Now, if I do a count(*) on '^rol', after the second query, this takes
> > > around 1 second, and returns 2528.
> > >
> > > On the other hand, if I do a count(*) on '^ric', his takes consequently
> > > around 1:30 mins, no matter how often I run it. This returns 7866.
> > >
> > > A search on count(*) of '^lling' and '^tones' takes around 2.5 secs after
> > > running it several times.

OK, I have the artist_fti table with 4.5 million rows, with an index
artist_fti_idx on artist_fti.string.  I don't have a 'product' table
because I don't have the disk space, but that is not really a problem
for testing.

I used the product table to populate the artits_fti, then deleted the
table so I could create the artist_fti_idx index.  Single table, no
join.

I am running on BSD/OS 3.1 PP200 with SCSI Ultra Barracuda disks.

I am seeing the same thing you are.  'lling' and 'tones' take 11-22
seconds on the first few runs, then return almost immediately.  If I do
another query and come back to the first one, I have the same slowness,
with the disk light being on almost constantly.  Certain queries seem to
lend themselves to speeding up, while 'rol' never seems to get really
fast.

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.

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.

I can attach to a running process, so when I attach to the backend, I
see:

#$ gdb /u/pg/bin/postgres 29755 GDB is free software and you are welcome
to distribute copies of it under certain conditions; type "show copying" to see the conditions.
There is absolutely no warranty for GDB; type "show warranty" for details.
GDB 4.16 (i386-unknown-bsdi3.0),
Copyright 1996 Free Software Foundation, Inc...

/usr/local/src/pgsql/pgsql/src/backend/commands/29755: No such file or
directory.
Attaching to program `/u/pg/bin/postgres', process 29755
0x94999 in WaitIO (buf=0x4c0ccc8, spinlock=0) at bufmgr.c:1107
1107            S_LOCK(&(buf->io_in_progress_lock));


---------------------------------------------------------------------------

Stepping to the next statement shows it is just hung here.

The backtrace shows:

---------------------------------------------------------------------------

#0  0x9499b in WaitIO (buf=0x4c0ccc8, spinlock=0) at bufmgr.c:1107
#1  0x94109 in BufferAlloc (reln=0x19df90, blockNum=11324,
    foundPtr=0xefbfb903 "\001\030����=\t", bufferLockHeld=0) at bufmgr.c:400
#2  0x93e91 in ReadBufferWithBufferLock (reln=0x19df90, blockNum=11324,
    bufferLockHeld=0) at bufmgr.c:255
#3  0x93dee in ReadBuffer (reln=0x19df90, blockNum=11324) at bufmgr.c:174
#4  0xb28f in heap_fetch (relation=0x19df90, seeself=0 '\000', tid=0x1b2256,
    b=0xefbfb974) at heapam.c:1050
#5  0x303d5 in rebuildheap (OIDNewHeap=11466400, OIDOldHeap=6424406,
    OIDOldIndex=11466368) at cluster.c:357
#6  0x30111 in cluster (oldrelname=0x112790 "artist_fti",
    oldindexname=0x10cdd0 "artist_fti_idx") at cluster.c:160
#7  0xa29c1 in ProcessUtility (parsetree=0x1127b0, dest=Remote)
    at utility.c:626


---------------------------------------------------------------------------

Nothing else is happening on my machine, so it must be locked against
itself.  I will check into this.

CLUSTER may be misbehaving on your machine too.

--
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)