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 self self total
> time seconds seconds calls 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 1000000 0.00 0.00 MBMatchText
> 5.38 1.10 0.22 1000000 0.00 0.00 HeapTupleSatisfiesSnapshot
> 5.13 1.31 0.21 999990 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.32 0.32 1000000 0.00 0.00 MBMatchText
> 7.75 0.61 0.29 1000000 0.00 0.00 HeapTupleSatisfiesSnapshot
> 6.42 0.85 0.24 1000000 0.00 0.00 slot_deform_tuple
> 5.88 1.07 0.22 8789050 0.00 0.00 pg_euc_mblen
> 5.88 1.29 0.22 1000012 0.00 0.00 heapgettup
> 5.61 1.50 0.21 999990 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.c 31 Dec 2004 22:01:22 -0000 1.59
--- src/backend/utils/adt/like.c 25 May 2005 22:24:46 -0000
***************
*** 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
