indexing words slow

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

Wow, this makes no sense to me.  How can this happen?  'rol' is fast, but
'ric' which returns 3 times as many rows takes 1.5 minutes, no matter how
often it is run?  And 64MB is a lot of memory.

Does profiling show that 'rol' and 'ric' take the same amount of CPU
time in the backend?  Is EXPLAIN identical for these two?  If so, there
must be something different about the I/O demands of the two queries,
but I can't really understand what that difference could be.  If I had
to take a guess, it is that 'ric' is somehow causing a cache flush for
each row it retrieves, while 'rol' fits in the cache.

Several million rows is a good sized table, but certainly not too big
for PostgreSQL.  I am sure people have larger tables.

I am kind of tempted to test it on my machine here.  I have BSD/OS with
a PP200 and 64MB RAM.  Can you send me something I can recreate here?
Perhaps put it on our ftp site?

>
> Running different queries in between affect these times.
>
> My computer has 64 Megs of RAM, and I'm running X (linux 2.0.30)
>
> So, it seems to me that with this amount of memory, my system is usable
> only for smaller tables (ie. no 550,000 rows in the main table, and no
> 4,500,000 rows in the 'index' table). If I want better performance for this
> setup, I need faster disks and more (how much more?) memory. Wish I could
> test this somehow.... Maybe I can ask my sysop at the university if I may
> test this on the dual PPro with 256 megs, but I don't think so....
>
> Maarten
>
>
> _____________________________________________________________________________
> | TU Delft, The Netherlands, Faculty of Information Technology and Systems  |
> |                   Department of Electrical Engineering                    |
> |           Computer Architecture and Digital Technique section             |
> |                          M.Boekhold@et.tudelft.nl                         |
> -----------------------------------------------------------------------------
>
>


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