Re: indexing words slow

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.

btw. to make things clearer on what I mean with '^lling' and '^tones', I
really mean "'^lling' *AND* '^tones'", ie. a join :) actually pretty good
don't ya think? :)

> 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

Profiling show different time (I guess you're talking about the
cumulative time for the query?). 'rol' takes 0.68 secs cumulative, 'ric'
takes 1.69 secs cumulative. EXPLAIN's are identical.

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

I *can* put something up through http on my universiy machine I guess.
It'll be a pg_dump of that table. Note however that this is generated
from a commercial database, so please promise me to delete it after
testing :) don't wanna get into any legal trouble (ah what, who's gonna
find out :).

I'll prepare a mail for you (ie. bruce) either tonight or tomorrow
morning (CET), with the source and a 'psql script' to set things up, as a
tar-file. The data itself you'll have to get through the web, much to
large to mail (over 10 megs gzip -9).

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