Thread

1. Re: Possible solution for LIKE optimization

   Giles Lean <giles@nemeton.com.au> — 2001-08-06T21:46:48Z

   [ I realise the discussion has left strxfrm(), but for the archives
     if nothing else ... ]
   
   Peter Eisentraut <peter_e@gmx.net> wrote:
   
   > We'd need to implement the strxfrm() function in SQL and the $<=$
   > operator, both of which are trivial.  The index would have to be in terms
   > of strxfrm().  There might be other issues, but they could be solved
   > algorithmically, I suppose.
   
   Implementations of strxfrm() that I've looked at have had result data
   that is three or four times larger than then input string -- quite a
   penalty in some situations.
   
   Regards,
   
   Giles
   
   
   
   

2. Re: Possible solution for LIKE optimization

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-08-06T22:21:48Z

   Giles Lean <giles@nemeton.com.au> writes:
   > Implementations of strxfrm() that I've looked at have had result data
   > that is three or four times larger than then input string -- quite a
   > penalty in some situations.
   
   Especially so given that we don't have TOAST for indexes, so the indexed
   value can't exceed about 2700 bytes (for btree and an 8K block size).
   You are allowed to compress first, so that's not a hard limit, but it
   could still be a problem.
   
   I like the non-locale-aware-opclass idea much better than the original.
   
   			regards, tom lane
   
   

3. Re: Possible solution for LIKE optimization

   Oleg Bartunov <oleg@sai.msu.su> — 2001-08-07T19:39:33Z

   On Mon, 6 Aug 2001, Tom Lane wrote:
   
   > Giles Lean <giles@nemeton.com.au> writes:
   > > Implementations of strxfrm() that I've looked at have had result data
   > > that is three or four times larger than then input string -- quite a
   > > penalty in some situations.
   >
   > Especially so given that we don't have TOAST for indexes, so the indexed
   > value can't exceed about 2700 bytes (for btree and an 8K block size).
   > You are allowed to compress first, so that's not a hard limit, but it
   > could still be a problem.
   >
   > I like the non-locale-aware-opclass idea much better than the original.
   
   Does this means that if implemented we could create indexes for
   different columns with/without locale support ? It's pain currently
   that I had to enable locale support while actually I need
   it only for several columns. Gnu sort on Linux 10 times slow if
   I use LC_ALL other than C !
   
   	Regards,
   
   		Oleg
   
   >
   > 			regards, tom lane
   >
   > ---------------------------(end of broadcast)---------------------------
   > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
   >
   
   	Regards,
   		Oleg
   _____________________________________________________________
   Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
   Sternberg Astronomical Institute, Moscow University (Russia)
   Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
   phone: +007(095)939-16-83, +007(095)939-23-83