Re: looking for a faster way to do that

Eduardo Morras wrote:

>> You can try these, i doubt they will use any index but its a
>> different approach:
>>
>> select * from items where length(items.code)<>length(rtrim(items.code,'ABC'));
>>
>> select * from items where strpos(items.code,'ABC')=0 or
>> strpos(items.code,'any_substring')=0;

Hi,

if I understand this right, it does not mean "check if the string appears at position 0"
which could translate into an index query, but rather "check if the string appears anywhere
and then check if that is position 0", so the entire table is checked.

explain analyze select items.num, wantcode from items, n where strpos(code, wantcode) = 0;
Nested Loop (cost=167.14..196066.54 rows=39178 width=36) (actual time=0.074..36639.312 rows=7832539 loops=1)
Join Filter: (strpos(("inner".code)::text, "outer".wantcode) = 0)
-> Seq Scan on n (cost=0.00..14.15 rows=815 width=32) (actual time=0.005..2.212 rows=815 loops=1)
-> Materialize (cost=167.14..263.28 rows=9614 width=42) (actual time=0.007..13.970 rows=9614 loops=815)
-> Seq Scan on items (cost=0.00..167.14 rows=9614 width=42) (actual time=0.044..14.855 rows=9614 loops=1)
Total runtime: 46229.836 ms

The query ran much faster than the pattern query, however. This seems to be the performance
of just searching for a plain string vs. initializing the regex engine every time (for 815
queries in a test set)

Regards
Wolfgang Hamann