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
Views: Raw Message | Whole Thread | Download mbox
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

Browse pgsql-patches by date

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