Thread

1. Re: [GENERAL] indexed regex select optimisation missing?

   selkovjr@mcs.anl.gov — 1999-11-04T05:23:12Z

   > select key from inv_word_i where word='whatever' order by count desc ;
   > 
   > and this is fast, however, if I use:
   > 
   > select key from inv_word_i where word~'^whatever.*' order by count desc ;
   > 
   > it is very slow.
   
   Did you try '^whatever' instead of '^whatever.*'? Based on common
   sense, the former should be much faster than the latter, which would
   match any cahracter any number of times, unless the regexp is
   optimized to avoid that.
   
   --Gene
   
   

2. Re: [GENERAL] indexed regex select optimisation missing?

   Stuart Woolford <stuartw@newmail.net> — 1999-11-04T09:13:19Z

   On Thu, 04 Nov 1999, Gene Selkov, Jr. wrote:
   > > select key from inv_word_i where word='whatever' order by count desc ;
   > > 
   > > and this is fast, however, if I use:
   > > 
   > > select key from inv_word_i where word~'^whatever.*' order by count desc ;
   > > 
   > > it is very slow.
   > 
   > Did you try '^whatever' instead of '^whatever.*'? Based on common
   > sense, the former should be much faster than the latter, which would
   > match any cahracter any number of times, unless the regexp is
   > optimized to avoid that.
   
   unfortunately '^whatever.*' is what I'm trying to locate (ie: all words
   starting with whatever, but with nay trailing text), the problem seems to be in
   the termination of the index scan, not in the actual regex match (which actually
   seems very good, speed wise..) otherwise I could just use ='whatever', which
   runs very very fast.
   
   I've had one hint that I need to build with --enable-locale, which I have to
   look into, so far I've just been using the redhat RPMs, so have not actually
   delved the build options, but I would have thought this would be a safe option
   everywhere (but thinking about it, character order is locale specific, so maybe
   it's not.. I'll look, if this is the case, enable-locale is a VERY important
   option for text searching, perhaps should be added the the section 4.x in the
   FAQ where the regex optimisation is mentioned?)
   
    > 
   > --Gene
   -- 
   ------------------------------------------------------------
   Stuart Woolford, stuartw@newmail.net
   Unix Consultant.
   Software Developer.
   Supra Club of New Zealand.
   ------------------------------------------------------------
   
   

3. Re: [GENERAL] indexed regex select optimisation missing?

   Peter Eisentraut <e99re41@docs.uu.se> — 1999-11-04T09:40:21Z

   On Thu, 4 Nov 1999, Stuart Woolford wrote:
   
   > unfortunately '^whatever.*' is what I'm trying to locate (ie: all words
   > starting with whatever, but with nay trailing text), the problem seems to be in
   > the termination of the index scan, not in the actual regex match (which actually
   > seems very good, speed wise..) otherwise I could just use ='whatever', which
   > runs very very fast.
   
   Isn't "all words that start with whatever but without trailing text" the
   same as = 'whatever'? From a regex point of view '^whatever' and
   '^whatever.*' are exactly equivalent, but I can see where one could fail
   to optimize properly. But that could even be a problem in the regex
   library, which is not any of the PostgreSQL developers' domain and which
   is used in other products as well.
   
   	-Peter
   
   -- 
   Peter Eisentraut                  Sernanders vaeg 10:115
   peter_e@gmx.net                   75262 Uppsala
   http://yi.org/peter-e/            Sweden