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: 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

Reply via email to