Re: [PATCHES] wchareq improvement
Bruce Momjian wrote: > > Patch applied with adjustment --- the second part of your patch that > skips comparing the first byte seemed unnecessary. It seemed likely > to cause a cpu stall, so just doing the loop seemed faster. > > Did you test if the second part of your patch actually caused a speedup? Well, I measured the performance today. As a result, I confirmed the second part of my patch is unnecessary as you pointed it out. Thanks for comment and applying patch. > a_ogawa wrote: > > > > In SQL that uses 'like' operator, wchareq is used to compare characters. > > > > At the head of wchareq, length of (multibyte) character is compared by > > using pg_mblen. Therefore, pg_mblen is executed many times, and it > > becomes a bottleneck. regards, --- Atsushi Ogawa ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] wchareq improvement
Patch applied with adjustment --- the second part of your patch that
skips comparing the first byte seemed unnecessary. It seemed likely
to cause a cpu stall, so just doing the loop seemed faster.
Did you test if the second part of your patch actually caused a speedup?
---
a_ogawa wrote:
>
> I forgot to attach a patch. I do post once again.
> In SQL that uses 'like' operator, wchareq is used to compare characters.
>
> At the head of wchareq, length of (multibyte) character is compared by
> using pg_mblen. Therefore, pg_mblen is executed many times, and it
> becomes a bottleneck.
>
> This patch makes a short cut, and reduces execution frequency of pg_mblen.
>
> test.sql:
> select count(*) from accounts
> where aid like '%1';
> ... (repeated 10 times)
>
> test command:
> $ psql -f test.sql
>
> result of original code(compile option "-O2 -pg"):
> ---
> Each sample counts as 0.01 seconds.
> % cumulative selfself total
> time seconds secondscalls s/call s/call name
> 7.82 0.32 0.32 17566930 0.00 0.00 pg_euc_mblen
> 7.09 0.61 0.29 17566930 0.00 0.00 pg_mblen
> 6.60 0.88 0.27 100 0.00 0.00 MBMatchText
> 5.38 1.10 0.22 100 0.00 0.00 HeapTupleSatisfiesSnapshot
> 5.13 1.31 0.21 90 0.00 0.00 ExecMakeFunctionResultNoSets
> 4.89 1.51 0.20 17566930 0.00 0.00 pg_eucjp_mblen
>
> result of patched code(compile option "-O2 -pg"):
>
> Each sample counts as 0.01 seconds.
> % cumulative self self total
> time seconds seconds calls s/call s/call name
> 8.56 0.320.32 100 0.00 0.00 MBMatchText
> 7.75 0.610.29 100 0.00 0.00 HeapTupleSatisfiesSnapshot
> 6.42 0.850.24 100 0.00 0.00 slot_deform_tuple
> 5.88 1.070.22 8789050 0.00 0.00 pg_euc_mblen
> 5.88 1.290.22 112 0.00 0.00 heapgettup
> 5.61 1.500.2190 0.00 0.00 ExecMakeFunctionResultNoSets
>
> execution time(compile option "-O2"):
> original code: 4.795sec
> patched code: 4.496sec
>
> regards,
>
> --- Atsushi Ogawa
[ Attachment, skipping... ]
>
> ---(end of broadcast)---
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
Index: src/backend/utils/adt/like.c
===
RCS file: /cvsroot/pgsql/src/backend/utils/adt/like.c,v
retrieving revision 1.59
diff -c -c -r1.59 like.c
*** src/backend/utils/adt/like.c31 Dec 2004 22:01:22 - 1.59
--- src/backend/utils/adt/like.c25 May 2005 22:24:46 -
***
*** 50,61
static int
wchareq(unsigned char *p1, unsigned char *p2)
{
! int l;
! l = pg_mblen(p1);
! if (pg_mblen(p2) != l)
return (0);
! while (l--)
{
if (*p1++ != *p2++)
return (0);
--- 50,67
static int
wchareq(unsigned char *p1, unsigned char *p2)
{
! int p1_len;
! /* Optimization: quickly compare the first byte. */
! if(*p1 != *p2)
return (0);
!
! p1_len = pg_mblen(p1);
! if (pg_mblen(p2) != p1_len)
! return (0);
!
! /* They are the same length */
! while (p1_len--)
{
if (*p1++ != *p2++)
return (0);
*** ./src/backend/utils/adt/like.c.orig Tue Apr 12 17:58:22 2005
--- ./src/backend/utils/adt/like.c Tue Apr 12 18:45:07 2005
***
*** 52,60
--- 52,75
{
int l;
+ /*
+* short cut. When first byte of p1 and p2 is different, these
+* characters will not match.
+*/
+ if(*p1 != *p2)
+ return (0);
+
l = pg_mblen(p1);
if (pg_mblen(p2) != l)
return (0);
+
+ /*
+* Skip first byte of p1 and p2. These are already checked at
+* top of this function.
+*/
+ l--;
+ p1++;
+ p2++;
while (l--)
{
---(end of broadcast)---
TIP 8: explain analyze is your friend
[PATCHES] wchareq improvement
I forgot to attach a patch. I do post once again. In SQL that uses 'like' operator, wchareq is used to compare characters. At the head of wchareq, length of (multibyte) character is compared by using pg_mblen. Therefore, pg_mblen is executed many times, and it becomes a bottleneck. This patch makes a short cut, and reduces execution frequency of pg_mblen. test.sql: select count(*) from accounts where aid like '%1'; ... (repeated 10 times) test command: $ psql -f test.sql result of original code(compile option "-O2 -pg"): --- Each sample counts as 0.01 seconds. % cumulative selfself total time seconds secondscalls s/call s/call name 7.82 0.32 0.32 17566930 0.00 0.00 pg_euc_mblen 7.09 0.61 0.29 17566930 0.00 0.00 pg_mblen 6.60 0.88 0.27 100 0.00 0.00 MBMatchText 5.38 1.10 0.22 100 0.00 0.00 HeapTupleSatisfiesSnapshot 5.13 1.31 0.21 90 0.00 0.00 ExecMakeFunctionResultNoSets 4.89 1.51 0.20 17566930 0.00 0.00 pg_eucjp_mblen result of patched code(compile option "-O2 -pg"): Each sample counts as 0.01 seconds. % cumulative self self total time seconds seconds calls s/call s/call name 8.56 0.320.32 100 0.00 0.00 MBMatchText 7.75 0.610.29 100 0.00 0.00 HeapTupleSatisfiesSnapshot 6.42 0.850.24 100 0.00 0.00 slot_deform_tuple 5.88 1.070.22 8789050 0.00 0.00 pg_euc_mblen 5.88 1.290.22 112 0.00 0.00 heapgettup 5.61 1.500.2190 0.00 0.00 ExecMakeFunctionResultNoSets execution time(compile option "-O2"): original code: 4.795sec patched code: 4.496sec regards, --- Atsushi Ogawa wchareq.patch Description: Binary data ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PATCHES] wchareq improvement
In SQL that uses 'like' operator, wchareq is used to compare character. At the head of wchareq, length of character is compared by using pg_mblen. Therefore, pg_mblen is executed many times, and it becomes a bottleneck. This patch makes a short cut, and reduces execution frequency of pg_mblen. test.sql: select count(*) from accounts where aid like '%1'; ... (repeated 10 times) test command: $ psql -f test.sql result of original code(compile option "-O2 -pg"): --- Each sample counts as 0.01 seconds. % cumulative selfself total time seconds secondscalls s/call s/call name 7.82 0.32 0.32 17566930 0.00 0.00 pg_euc_mblen 7.09 0.61 0.29 17566930 0.00 0.00 pg_mblen 6.60 0.88 0.27 100 0.00 0.00 MBMatchText 5.38 1.10 0.22 100 0.00 0.00 HeapTupleSatisfiesSnapshot 5.13 1.31 0.21 90 0.00 0.00 ExecMakeFunctionResultNoSets 4.89 1.51 0.20 17566930 0.00 0.00 pg_eucjp_mblen result of patched code(compile option "-O2 -pg"): Each sample counts as 0.01 seconds. % cumulative self self total time seconds seconds calls s/call s/call name 8.56 0.320.32 100 0.00 0.00 MBMatchText 7.75 0.610.29 100 0.00 0.00 HeapTupleSatisfiesSnapshot 6.42 0.850.24 100 0.00 0.00 slot_deform_tuple 5.88 1.070.22 8789050 0.00 0.00 pg_euc_mblen 5.88 1.290.22 112 0.00 0.00 heapgettup 5.61 1.500.2190 0.00 0.00 ExecMakeFunctionResultNoSets execution time(compile option "-O2"): original code: 4.795sec patched code: 4.496sec regards, --- Atsushi Ogawa ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
