
> ------------- shell script -------------------
> for i in 32 64 128 256 512 1024 2048 4096 8192
> do
> psql -c "explain analyze select liketest(a,'aaa') from 
> (select substring('very_long_text' from 0 for $i) as a) as a" test
> done
> ------------- shell script -------------------

I don't think your search string is sufficient for a test. 
With 'aaa' it actually knows that it only needs to look at the 
first three characters of a. Imho you need to try something 
like liketest(a,'%aaa%').

Andreas 

Wed, 3 Oct 2001 10:09:46 +0200
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: Unicode combining characters 
> I don't think your search string is sufficient for a test. 
> With 'aaa' it actually knows that it only needs to look at the 
> first three characters of a. Imho you need to try something 
> like liketest(a,'%aaa%').

Ok. I ran the modified test (now the iteration is reduced to 100000 in
liketest()). As you can see, there's huge difference. MB seems up to
~8 times slower:-< There seems some problems existing in the
implementation. Considering REGEX is not so slow, maybe we should
employ the same design as REGEX. i.e. using wide charcters, not
multibyte streams...

MB+LIKE
Total runtime: 1321.58 msec
Total runtime: 1718.03 msec
Total runtime: 2519.97 msec
Total runtime: 4187.05 msec
Total runtime: 7629.24 msec
Total runtime: 14456.45 msec
Total runtime: 17320.14 msec
Total runtime: 17323.65 msec
Total runtime: 17321.51 msec

noMB+LIKE
Total runtime: 964.90 msec
Total runtime: 993.09 msec
Total runtime: 1057.40 msec
Total runtime: 1192.68 msec
Total runtime: 1494.59 msec
Total runtime: 2078.75 msec
Total runtime: 2328.77 msec
Total runtime: 2326.38 msec
Total runtime: 2330.53 msec
--
Tatsuo Ishii

Wed, 03 Oct 2001 18:30:01 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: Unicode combining characters 
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> ... There seems some problems existing in the
> implementation. Considering REGEX is not so slow, maybe we should
> employ the same design as REGEX. i.e. using wide charcters, not
> multibyte streams...

Seems like a good thing to put on the to-do list.  In the meantime,
we still have the question of whether to enable multibyte in the
default configuration.  I'd still vote YES, as these results seem
to me to demonstrate that there is no wide-ranging performance penalty.
A problem confined to LIKE on long strings isn't a showstopper IMHO.

			regards, tom lane

Wed, 03 Oct 2001 10:56:04 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Unicode combining characters 
> Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> > ... There seems some problems existing in the
> > implementation. Considering REGEX is not so slow, maybe we should
> > employ the same design as REGEX. i.e. using wide charcters, not
> > multibyte streams...
> 
> Seems like a good thing to put on the to-do list.  In the meantime,
> we still have the question of whether to enable multibyte in the
> default configuration.  I'd still vote YES, as these results seem
> to me to demonstrate that there is no wide-ranging performance penalty.
> A problem confined to LIKE on long strings isn't a showstopper IMHO.
> 

Added to TODO:

* Use wide characters to evaluate regular expressions, for performance
(Tatsuo) 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 3 Oct 2001 12:05:34 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Unicode combining characters
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Added to TODO:

> * Use wide characters to evaluate regular expressions, for performance
> (Tatsuo) 

Regexes are fine; it's LIKE that's slow.

			regards, tom lane

Wed, 03 Oct 2001 13:35:07 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Unicode combining characters 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Added to TODO:
> 
> > * Use wide characters to evaluate regular expressions, for performance
> > (Tatsuo) 
> 
> Regexes are fine; it's LIKE that's slow.

Oops, thanks.  Changed to:

* Use wide characters to evaluate LIKE, for performance (Tatsuo)        
   
-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 3 Oct 2001 13:38:45 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Unicode combining characters
Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane writes:
>> In the meantime, we still have the question of whether to enable
>> multibyte in the default configuration.

> Perhaps we could make it a release goal for 7.3

Yeah, that's probably the best way to proceed... it's awfully late
in the 7.2 cycle to be deciding to do this now...

			regards, tom lane

Wed, 03 Oct 2001 16:44:32 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Unicode combining characters 
Tom Lane writes:

> In the meantime, we still have the question of whether to enable
> multibyte in the default configuration.

This would make more sense if all of multibyte, locale, and NLS became
defaults in one release.  I haven't quite sold people in the second item
yet, although I have a design how to do that (see below).  And the third,
well who knows...

Perhaps we could make it a release goal for 7.3 to

* Optimize i18n stuff to have a minimal performance penalty when it's not
  used. (locale=C etc.)

* Make i18n stuff sufficiently well-behaved to make it the default.
  (Especially, add initdb options and GUC parameters to set the locale.
  Don't rely on environment variables -- too complicated.)

Meanwhile, quadratic performance penalties (or so it seems) for LIKE
expressions aren't exactly a "minor" problem.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Wed, 3 Oct 2001 22:46:55 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Unicode combining characters 
> Tom Lane writes:
> 
> > In the meantime, we still have the question of whether to enable
> > multibyte in the default configuration.
> 
> This would make more sense if all of multibyte, locale, and NLS became
> defaults in one release.  I haven't quite sold people in the second item
> yet, although I have a design how to do that (see below).  And the third,
> well who knows...
> 
> Perhaps we could make it a release goal for 7.3 to
> 
> * Optimize i18n stuff to have a minimal performance penalty when it's not
>   used. (locale=C etc.)
> 
> * Make i18n stuff sufficiently well-behaved to make it the default.
>   (Especially, add initdb options and GUC parameters to set the locale.
>   Don't rely on environment variables -- too complicated.)
> 
> Meanwhile, quadratic performance penalties (or so it seems) for LIKE
> expressions aren't exactly a "minor" problem.
> 

Added to TODO:

* Optimize locale to have minimal performance impact when not used
(Peter E)
-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 3 Oct 2001 18:27:47 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Unicode combining characters
> Ok. I ran the modified test (now the iteration is reduced to 100000 in
> liketest()). As you can see, there's huge difference. MB seems up to
> ~8 times slower:-< There seems some problems existing in the
> implementation. Considering REGEX is not so slow, maybe we should
> employ the same design as REGEX. i.e. using wide charcters, not
> multibyte streams...
> 
> MB+LIKE
> Total runtime: 1321.58 msec
> Total runtime: 1718.03 msec
> Total runtime: 2519.97 msec
> Total runtime: 4187.05 msec
> Total runtime: 7629.24 msec
> Total runtime: 14456.45 msec
> Total runtime: 17320.14 msec
> Total runtime: 17323.65 msec
> Total runtime: 17321.51 msec
> 
> noMB+LIKE
> Total runtime: 964.90 msec
> Total runtime: 993.09 msec
> Total runtime: 1057.40 msec
> Total runtime: 1192.68 msec
> Total runtime: 1494.59 msec
> Total runtime: 2078.75 msec
> Total runtime: 2328.77 msec
> Total runtime: 2326.38 msec
> Total runtime: 2330.53 msec

I did some trials with wide characters implementation and saw
virtually no improvement. My guess is the logic employed in LIKE is
too simple to hide the overhead of the multibyte and wide character
conversion. The reason why REGEX with MB is not so slow would be the
complexity of its logic, I think. As you can see in my previous
postings, $1 ~ $2 operation (this is logically same as a LIKE '%a%')
is, for example, almost 80 times slower than LIKE (remember that
likest() loops over 10 times more than regextest()).

So I decided to use a completely different approach. Now like has two
matching engines, one for single byte encodings (MatchText()), the
other is for multibyte ones (MBMatchText()). MatchText() is identical
to the non MB version of it, and virtually no performance penalty for
single byte encodings. MBMatchText() is for multibyte encodings and is
identical the one used in 7.1.

Here is the MB case result with SQL_ASCII encoding.

Total runtime: 901.69 msec
Total runtime: 939.08 msec
Total runtime: 993.60 msec
Total runtime: 1148.18 msec
Total runtime: 1434.92 msec
Total runtime: 2024.59 msec
Total runtime: 2288.50 msec
Total runtime: 2290.53 msec
Total runtime: 2316.00 msec

To accomplish this, I moved MatchText etc. to a separate file and now
like.c includes it *twice* (similar technique used in regexec()). This
makes like.o a little bit larger, but I believe this is worth for the
optimization.
--
Tatsuo Ishii

Thu, 04 Oct 2001 11:16:42 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: Unicode combining characters 
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> To accomplish this, I moved MatchText etc. to a separate file and now
> like.c includes it *twice* (similar technique used in regexec()). This
> makes like.o a little bit larger, but I believe this is worth for the
> optimization.

That sounds great.

What's your feeling now about the original question: whether to enable
multibyte by default now, or not?  I'm still thinking that Peter's
counsel is the wisest: plan to do it in 7.3, not today.  But this fix
seems to eliminate the only hard reason we have not to do it today ...

			regards, tom lane

Wed, 03 Oct 2001 23:05:16 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Unicode combining characters 
> What's your feeling now about the original question: whether to enable
> multibyte by default now, or not?  I'm still thinking that Peter's
> counsel is the wisest: plan to do it in 7.3, not today.  But this fix
> seems to eliminate the only hard reason we have not to do it today ...

If SQL99's I18N staffs would be added in 7.3, it means both the
multibyte support and the locale support might disappear, then 2might
be merged into it (not sure about NLS. is that compatible with, for
example, per column charset?)

So, for none multibyte users, the multibyte support would be
one-release-only-functionality: suddenly appears in 7.2 and disappears
in 7.3. I'm afraid this would cause more troubles rather than
usefullness.

What do you think?
--
Tatsuo Ishii

Thu, 04 Oct 2001 12:43:23 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: Unicode combining characters 
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> What do you think?

I think that we were supposed to go beta a month ago, and so this is
no time to start adding new features to this release.  Let's plan to
make this happen (one way or the other) in 7.3, instead.

			regards, tom lane

Wed, 03 Oct 2001 23:49:49 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Unicode combining characters 
> I think that we were supposed to go beta a month ago, and so this is
> no time to start adding new features to this release.  Let's plan to
> make this happen (one way or the other) in 7.3, instead.

Agreed.
--
Tatsuo Ishii

Thu, 04 Oct 2001 13:11:21 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: Unicode combining characters 
> > Ok. I ran the modified test (now the iteration is reduced to 100000 in
> > liketest()). As you can see, there's huge difference. MB seems up to
> > ~8 times slower:-< There seems some problems existing in the
> > implementation. Considering REGEX is not so slow, maybe we should
> > employ the same design as REGEX. i.e. using wide charcters, not
> > multibyte streams...

Let me add I think our regex code is very slow.  It is the standard BSD
regex library by Henry Spencer.  He rewrote it a few years ago for TCL
8.X and said he was working on a standalone library version.  I have
asked him several times via email over the years but he still has not
released a standalone version of the new optimized regex code.  It is on
our TODO list.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 4 Oct 2001 00:30:13 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Unicode combining characters
Hi,

I should have sent the patch earlier, but got delayed by other stuff.
Anyway, here is the patch:

- most of the functionality is only activated when MULTIBYTE is
  defined,

- check valid UTF-8 characters, client-side only yet, and only on
  output, you still can send invalid UTF-8 to the server (so, it's
  only partly compliant to Unicode 3.1, but that's better than
  nothing).

- formats with the correct number of columns (that's why I made it in
  the first place after all), but only for UNICODE. However, the code
  allows to plug-in routines for other encodings, as Tatsuo did for
  the other multibyte functions.

- corrects a bit the UTF-8 code from Tatsuo to allow Unicode 3.1
  characters (characters with values >= 0x10000, which are encoded on
  four bytes).

- doesn't depend on the locale capabilities of the glibc (useful for
  remote telnet).

I would like somebody to check it closely, as it is my first patch to
pgsql.  Also, I created dummy .orig files, so that the two files I
created are included, I hope that's the right way.

Now, a lot of functionality is NOT included here, but I will keep that
for 7.3 :) That includes all string checking on the server side (which
will have to be a bit more optimised ;) ), and the input checking on
the client side for UTF-8, though that should not be difficult. It's
just to send the strings through mbvalidate() before sending them to
the server. Strong checking on UTF-8 strings is mandatory to be
compliant with Unicode 3.1+ .

Do I have time to look for a patch to include iso-8859-15 for 7.2 ?
The euro is coming 1. january 2002 (before 7.3 !) and over 280
millions people in Europe will need the euro sign and only iso-8859-15
and iso-8859-16 have it (and unfortunately, I don't think all Unices
will switch to Unicode in the meantime)....

err... yes, I know that this is not every single person in Europe that
uses PostgreSql, so it's not exactly 280m, but it's just a matter of
time ! ;)

I'll come back (on pgsql-hackers) later to ask a few questions
regarding the full unicode support (normalisation, collation,
regexes,...) on the server side :)

Here is the patch !

Patrice.

-- 
Patrice Hï¿œDï¿œ ------------------------------- patrice ï¿œ islande org -----
  --  Isn't it weird  how scientists  can imagine  all the matter of the
universe exploding out of a dot smaller than the head of a pin, but they
can't come up with a more evocative name for it than "The Big Bang" ?
  -- What would _you_ call the creation of the universe ?
  -- "The HORRENDOUS SPACE KABLOOIE !"               - Calvin and Hobbes
------------------------------------------ http://www.islande.org/ -----
Mon, 8 Oct 2001 21:35:44 +0200
Patrice =?iso-8859-15?Q?H=E9d=E9?= <phede-ml@islande.org>
Re: [HACKERS] Unicode combining characters
> - corrects a bit the UTF-8 code from Tatsuo to allow Unicode 3.1
>   characters (characters with values >= 0x10000, which are encoded on
>   four bytes).

After applying your patches, do the 4-bytes UTF-8 convert to UCS-2 (2
bytes) or UCS-4 (4 bytes) in pg_utf2wchar_with_len()? If it were 4
bytes, we are in trouble. Current regex implementaion does not handle
4 byte width charsets.
--
Tatsuo Ishii

Tue, 09 Oct 2001 23:16:56 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: [HACKERS] Unicode combining characters
* Tatsuo Ishii <t-ishii@sra.co.jp> [011009 18:38]:
> > - corrects a bit the UTF-8 code from Tatsuo to allow Unicode 3.1
> >   characters (characters with values >= 0x10000, which are encoded on
> >   four bytes).
> 
> After applying your patches, do the 4-bytes UTF-8 convert to UCS-2 (2
> bytes) or UCS-4 (4 bytes) in pg_utf2wchar_with_len()? If it were 4
> bytes, we are in trouble. Current regex implementaion does not handle
> 4 byte width charsets.

*sigh* yes, it does encode to four bytes :(

Three solutions then :

1) we support these supplementary characters, knowing that they won't
   work with regexes,

2) I back out the change, but then anyone using these characters will
   get something weird, since the decoding would be faulty (they would
   be handled as 3 bytes UTF-8 chars, and then the fourth byte would
   become a "faulty char"... not very good, as the 3-byte version is
   still not a valid UTF-8 code !),

3) we fix the regex engine within the next 24 hours, before the beta
   deadline is activated :/

I must say that I doubt that anyone will use these characters in the
next few months : these are mostly chinese extended characters, with
old italic, deseret, and gothic scripts, and bysantine and western
musical symbols, as well as the mathematical alphanumerical symbols.

I would prefer solution 1), as I think it is better to allow these
characters, even with a temporary restriction on the regex, than to
fail completely on them. As for solution 3), we may still work at it
in the next few months :) [I haven't even looked at the regex engine
yet, so I don't know the implications of what I have just said !]

What do you think ?

Patrice

-- 
Patrice Hï¿œdï¿œ
email: patrice hede ï¿œ islande org
www  : http://www.islande.org/

Tue, 9 Oct 2001 19:07:38 +0200
Patrice =?iso-8859-15?Q?H=E9d=E9?= <phede-ml@islande.org>
Re: [HACKERS] Unicode combining characters
> > After applying your patches, do the 4-bytes UTF-8 convert to UCS-2 (2
> > bytes) or UCS-4 (4 bytes) in pg_utf2wchar_with_len()? If it were 4
> > bytes, we are in trouble. Current regex implementaion does not handle
> > 4 byte width charsets.
> 
> *sigh* yes, it does encode to four bytes :(
> 
> Three solutions then :
> 
> 1) we support these supplementary characters, knowing that they won't
>    work with regexes,
> 
> 2) I back out the change, but then anyone using these characters will
>    get something weird, since the decoding would be faulty (they would
>    be handled as 3 bytes UTF-8 chars, and then the fourth byte would
>    become a "faulty char"... not very good, as the 3-byte version is
>    still not a valid UTF-8 code !),
> 
> 3) we fix the regex engine within the next 24 hours, before the beta
>    deadline is activated :/
> 
> I must say that I doubt that anyone will use these characters in the
> next few months : these are mostly chinese extended characters, with
> old italic, deseret, and gothic scripts, and bysantine and western
> musical symbols, as well as the mathematical alphanumerical symbols.
> 
> I would prefer solution 1), as I think it is better to allow these
> characters, even with a temporary restriction on the regex, than to
> fail completely on them. As for solution 3), we may still work at it
> in the next few months :) [I haven't even looked at the regex engine
> yet, so I don't know the implications of what I have just said !]
> 
> What do you think ?

I think 2) is not very good, and we should reject these 4-bytes UTF-8
strings. After all, we are not ready for them.

BTW, other part of your patches looks good. Peter, what do you think?
--
Tatsuo Ishii

Wed, 10 Oct 2001 10:12:01 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: [HACKERS] Unicode combining characters
> > 1) we support these supplementary characters, knowing that they won't
> >    work with regexes,
> > 
> > 2) I back out the change, but then anyone using these characters will
> >    get something weird, since the decoding would be faulty (they would
> >    be handled as 3 bytes UTF-8 chars, and then the fourth byte would
> >    become a "faulty char"... not very good, as the 3-byte version is
> >    still not a valid UTF-8 code !),
> > 
> > 3) we fix the regex engine within the next 24 hours, before the beta
> >    deadline is activated :/
> > 
> > What do you think ?
> 
> I think 2) is not very good, and we should reject these 4-bytes UTF-8
> strings. After all, we are not ready for them.

If we still recognise them as 4-byte UTF-8 chars (in order to parse
the next char correctly) and reject them as invalid chars, that should
be OK :)

> BTW, other part of your patches looks good. Peter, what do you think?

Nice to hear :)

Patrice

-- 
Patrice Hï¿œdï¿œ
email: patrice hede ï¿œ islande org
www  : http://www.islande.org/

Wed, 10 Oct 2001 19:28:19 +0200
Patrice =?iso-8859-15?Q?H=E9d=E9?= <phede-ml@islande.org>
Re: [HACKERS] Unicode combining characters
I have committed part of Patrice's patches with minor fixes.
Uncommitted changes are related to the backend side, and the reason
could be found in the previous discussions (basically this is due to
the fact that current regex code does not support UTF-8 chars >=
0x10000). Instead pg_veryfymbstr() now rejects UTF-8 chars >= 0x10000.
--
Tatsuo Ishii

> Hi,
> 
> I should have sent the patch earlier, but got delayed by other stuff.
> Anyway, here is the patch:
> 
> - most of the functionality is only activated when MULTIBYTE is
>   defined,
> 
> - check valid UTF-8 characters, client-side only yet, and only on
>   output, you still can send invalid UTF-8 to the server (so, it's
>   only partly compliant to Unicode 3.1, but that's better than
>   nothing).
> 
> - formats with the correct number of columns (that's why I made it in
>   the first place after all), but only for UNICODE. However, the code
>   allows to plug-in routines for other encodings, as Tatsuo did for
>   the other multibyte functions.
> 
> - corrects a bit the UTF-8 code from Tatsuo to allow Unicode 3.1
>   characters (characters with values >= 0x10000, which are encoded on
>   four bytes).
> 
> - doesn't depend on the locale capabilities of the glibc (useful for
>   remote telnet).
> 
> I would like somebody to check it closely, as it is my first patch to
> pgsql.  Also, I created dummy .orig files, so that the two files I
> created are included, I hope that's the right way.
> 
> Now, a lot of functionality is NOT included here, but I will keep that
> for 7.3 :) That includes all string checking on the server side (which
> will have to be a bit more optimised ;) ), and the input checking on
> the client side for UTF-8, though that should not be difficult. It's
> just to send the strings through mbvalidate() before sending them to
> the server. Strong checking on UTF-8 strings is mandatory to be
> compliant with Unicode 3.1+ .
> 
> Do I have time to look for a patch to include iso-8859-15 for 7.2 ?
> The euro is coming 1. january 2002 (before 7.3 !) and over 280
> millions people in Europe will need the euro sign and only iso-8859-15
> and iso-8859-16 have it (and unfortunately, I don't think all Unices
> will switch to Unicode in the meantime)....
> 
> err... yes, I know that this is not every single person in Europe that
> uses PostgreSql, so it's not exactly 280m, but it's just a matter of
> time ! ;)
> 
> I'll come back (on pgsql-hackers) later to ask a few questions
> regarding the full unicode support (normalisation, collation,
> regexes,...) on the server side :)
> 
> Here is the patch !
> 
> Patrice.
> 
> -- 
> Patrice H���D��� ------------------------------- patrice ��� islande org -----
>   --  Isn't it weird  how scientists  can imagine  all the matter of the
> universe exploding out of a dot smaller than the head of a pin, but they
> can't come up with a more evocative name for it than "The Big Bang" ?
>   -- What would _you_ call the creation of the universe ?
>   -- "The HORRENDOUS SPACE KABLOOIE !"               - Calvin and Hobbes
> ------------------------------------------ http://www.islande.org/ -----

Mon, 15 Oct 2001 10:26:19 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: [PATCHES] Unicode combining characters