SQLize Online / PHPize Online  /  SQLtest Online

create table tst1 ( id bigint primary key, key text not null, value text not null ); insert into tst1 (id, key, value) select i, 'k'||i, 'v'||(i*7) from generate_series(1, 100000) g (i); create index idx_tst1_key on tst1 (key asc); vacuum analyze tst1; select pg_size_pretty(pg_indexes_size('tst1')); -- as expected: index scan explain (analyze, buffers) select id from tst1 where key = 'v111'; -- seq scan because this index can not work with `like` explain (analyze, buffers) select id from tst1 where key like 'v111%'; drop index idx_tst1_key; create index idx_tst1_key_c on tst1 (key collate "C" asc); select pg_size_pretty(pg_indexes_size('tst1')); -- now the index works explain (analyze, buffers) select id from tst1 where key like 'v111%'; -- but explain (analyze, buffers) select id from tst1 where key = 'v111'; -- haha, it does not work with equals drop index idx_tst1_key_c; create index idx_tst1_key_pattern on tst1 (key text_pattern_ops asc); select pg_size_pretty(pg_indexes_size('tst1')); -- now this works explain (analyze, buffers) select id from tst1 where key = 'v111'; -- and this works explain (analyze, buffers) select id from tst1 where key like 'v111%';