Re: [HACKERS] Re: indexing words slow

On Wed, 11 Mar 1998 ocie(at)paracel(dot)com wrote:

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

OK, I wasn't clear enough:

select count(*) from table t1, table t2 where t1.string ~ '^lling' and
t2.string '^tones';

Ofcourse the can be done (note that this is a table with 4,500,000 rows).

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