Re: [HACKERS] Re: indexing words slow

Maarten Boekhold wrote:
>
> 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? :)

This sounds like an unsatisfyable query, perhaps if the database
figured this out, it could return zero rows without even hitting the
index.  If the first item matched, the first character is an 'l', if
the second matches, a 't'.  It can't be both an 'l' and a 't'!

We could also do this for some other queries, like:

select ... from ... where a<b and b<c and c<d

If a and d are parameters to the query (constants), and d<a, then
there can never be a row returned.

Ocie