On Wed, 11 Mar 1998 ocie@paracel.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.Boekhold@et.tudelft.nl |
-----------------------------------------------------------------------------