Skip site navigation (1) Skip section navigation (2)

Re: wchareq improvement

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: "a_ogawa" <a_ogawa(at)hi-ho(dot)ne(dot)jp>
Cc: pgsql-patches(at)postgresql(dot)org
Subject: Re: wchareq improvement
Date: 2005-05-25 22:58:46
Message-ID: 200505252258.j4PMwlk29971@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-patches
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(at)candle(dot)pha(dot)pa(dot)us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

In response to

Responses

pgsql-patches by date

Next:From: Maura P. Jones, IIDate: 2005-05-25 23:46:23
Subject: Industry News
Previous:From: Bruce MomjianDate: 2005-05-25 22:12:48
Subject: Re: Not to to confusing

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group