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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
