sql Like alternate

Question

Is there any other solution available to use instead of Like to match starts with?

here is my query to match starts with using like.

explain analyze select * from completedcalls where call_id like 'GWYA4NvSLzoIcvA7RAtmn_a9IelwOQeH@209.44.103.3%';
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Seq Scan on completedcalls  (cost=0.00..52659.96 rows=112 width=228) (actual time=1.541..249.857 rows=2 loops=1)
   Filter: ((call_id)::text ~~ 'GWYA4NvSLzoIcvA7RAtmn_a9IelwOQeH@209.44.103.3%'::text)
 Total runtime: 249.893 ms
(3 rows)

which is very expansive because it does sequence scan instead of index scan. due to nature of like it can not use index on provided column. index of column is simple as:

"i_call_id" btree (call_id)

Is there any special class of index which can help like to improve speed, or any other way to achieve same without using like?

Used Table script is:

              Table "public.completedcalls"
    Column     |           Type           |  Modifiers   
---------------+--------------------------+--------------
 call_id       | character varying(128)   | 
 sip_code      | integer                  | 
 duration      | integer                  | 
 setup_time    | timestamp with time zone | not null
 authname      | character varying(30)    | 
 src_sig_ip    | character varying(20)    | 
 dst_sig_ip    | character varying(20)    | 
 cld           | character varying(22)    | 
 cli           | character varying(22)    | 
Indexes:
    "i_call_id" btree (call_id)
    "i_dst_sig_ip" btree (dst_sig_ip)

Just throwing an idea out: what is the performance when subscripting the desired characters in `call_id` and comparing them to your string? — woemler, Jan 15 '13 at 20:42

score 3 · Accepted Answer · answered Jan 15 '13 at 20:48

3

This case of LIKE index use (or lack thereof) is described in the doc:

In short you should create the index as

create index i_call_id on completedcalls(call_id varchar_pattern_ops);

But read the page linked above for the caveats.

answered Jan 15 '13 at 20:48

Daniel Vérité

58,074
15
129
156

score 2 · Answer 2 · edited Jan 16 '13 at 07:11

2

LIKE statements can still be used with b-tree indexes, assuming that there is no leading wildcard:

The optimizer can also use a B-tree index for queries involving the pattern matching operators LIKE and ~ if the pattern is a constant and is anchored to the beginning of the string — for example, col LIKE 'foo%' or col ~ '^foo', but not col LIKE '%bar'.

If your index isn't being used, then it is for a reason other than the usage of LIKE shown here...

edited Jan 16 '13 at 07:11

Frank Heikens

117,544
24
142
135

answered Jan 15 '13 at 20:42

Michael Fredrickson

36,839
5
92
109

1

The question is tagged postgresql, so MySQL manual is not likely to help much – Daniel Vérité Jan 15 '13 at 20:50
Am using only one wildcard % in the end of call_id but explain clearly shows that it uses Seq Scan and also its on postgresql. – sharafjaffri Jan 15 '13 at 21:04
@DanielVérité Thanks, I didn't see that tag when I posted this answer... I updated to reference the corrisponding postgresql documentation. – Michael Fredrickson Jan 15 '13 at 21:04
Then why Its not using index scan? Is there any other bug in query? – sharafjaffri Jan 15 '13 at 21:16

score 1 · Answer 3 · edited Feb 15 '13 at 18:11

1

While I'm not sure if it would help the performance, have you looked into using regular expressions? You could use the caret ^ to return the records located at the beginning of the string.

Maybe something like this:

select * 
from completedcalls 
where call_id ~ '^GWYA4NvSLzoIcvA7RAtmn_a9IelwOQeH@209.44.103.3';

edited Feb 15 '13 at 18:11

wickedone

542
1
6
18

answered Jan 15 '13 at 20:42

sgeddes

62,311
6
61
83

score 1 · Answer 4 · answered Jan 15 '13 at 20:42

1

LIKE is able to use an index on a condition with a trailing %.

It's possible that the optimizer thinks a full scan would be better than an index scan, as the latter required additional table lookups for other columns and record visibility scope.

answered Jan 15 '13 at 20:42

Quassnoi

413,100
91
616
614

How can I provide hint to tell postgresql to use index scan? – sharafjaffri Jan 15 '13 at 21:10
@sharafjaffri: creators of `PostgreSQL` actively object implementin hints so no, you can't. Do you think you really need it? How many records are there in the table and how many of them do satisfy the condition? – Quassnoi Jan 15 '13 at 21:13
there are 1041583 records and 2 records satisfy this condition. – sharafjaffri Jan 15 '13 at 21:17

score 0 · Answer 5 · answered Jan 15 '13 at 20:42

From the looks of it, you should probably split up that call_id column into separate columns for each of the components. For example, you could add additional columns with indices for what looks like an ID and an IP address in your example, make those indices and then select on those.

sql Like alternate

5 Answers5