postgresql - What kind of index should I create to make "WHERE col1 LIKE '0000%' AND col2 = 'somevalue'" faster? -

postgresql - What kind of index should I create to make "WHERE col1 LIKE '0000%' AND col2 = 'somevalue'" faster? -

i tried next queries in order search within quad tree using postgresql's like operator. in column col3, there words '0133002112300300320' inserted, describes quad tree's path.

create table table1 (col1 character(9) not null, col2 integer not null, col3 character varying(64), col4 integer not null, col5 double precision not null, primary key(col1,col2,col3)); -- performs sequential search select col1,col2,col3,col4,col5 table1 col1='somevalue' , col2=0 , col3 '01330021123003003%';

the problem primary key index set doesn't work where col1='somevalue' , col2=0 , col3 '01330021123003003%'. seems can't utilize like operator and operator @ same time if want utilize created index.

are there special indices can create create select faster?

it seems can't utilize operator , operator @ same time if want utilize created index.

the index can used in case. here's how exact table , exact query, random well-distributed contents among 100k rows:

insert table1 select (random()*10000)::int, (random()*10000)::int, md5(random()::text), 0,0 generate_series(1,100000); analyze table1; explain analyze select col1,col2,col3,col4,col5 table1 col1='somevalue' , col2=0 , col3 '01330021123003003%';

result:

index scan using table1_pkey on table1 (cost=0.00..8.32 rows=1 width=59) (actual time=0.022..0.022 rows=0 loops=1) index cond: ((col1 = 'somevalue'::bpchar) , (col2 = 0)) filter: ((col3)::text ~~ '01330021123003003%'::text) total runtime: 0.050 ms (4 rows)

that index scan using table1_pkey shows index gets used query.

if doesn't dataset, plausible reason you're searching values common.

postgresql indexing database-performance like-operator