> Maybe something like this: declare a plpgsql function that takes two
> text parameters and has a body like
>
> for (i = 0 to a million)
> boolvar := $1 like $2;
>
> Then call it with strings of different lengths and see how the runtime
> varies. You need to apply the LIKE to function parameters, else the
> system will probably collapse the LIKE operation to a constant...
Good idea. I did tests for both LIKE and REGEX using PL/pgsql
functions(see source code below). Here are the result. What I did was
calling the functions with changing taret strings from 32byte to
8192. Times are all in msec.
(1) LIKE
bytes Without MB With MB
32 8121.94 8094.73
64 8167.98 8105.24
128 8151.30 8108.61
256 8090.12 8098.20
512 8111.05 8101.07
1024 8110.49 8099.61
2048 8095.32 8106.00
4096 8094.88 8091.19
8192 8123.02 8121.63
(2) REGEX
bytes Without MB With MB
32 117.93 119.47
64 126.41 127.61
128 143.97 146.55
256 180.49 183.69
512 255.53 256.16
1024 410.59 409.22
2048 5176.38 5181.99
4096 6000.82 5627.84
8192 6529.15 6547.10
------------- shell script -------------------
for i in 32 64 128 256 512 1024 2048 4096 8192
do
psql -c "explain analyze select liketest(a,'aaa') from (select substring('very_long_text' from 0 for $i) as a) as a"
test
done
------------- shell script -------------------
------------- functions -----------------
drop function liketest(text,text);
create function liketest(text,text) returns bool as '
declarei int;rtn boolean;
begini := 1000000;while i > 0 loop rtn := $1 like $2; i := i - 1;end loop;return rtn;
end;
' language 'plpgsql';
drop function regextest(text,text);
create function regextest(text,text) returns bool as '
declarei int;rtn boolean;
begini := 10000;while i > 0 loop rtn := $1 ~ $2; i := i - 1;end loop;return rtn;
end;
' language 'plpgsql';
------------- functions -----------------