PostgreSQL'de Like Sorgularını Optimize Etme

SQL sorgusu, PostgreSql'de like operatörü ile standart btree dizinleri kullanılarak optimize edilemez. Like operatörünü optimize etmek için trgm dizin oluşturmak üzere pg_trgm uzantısını kullanabilirsiniz. pg_trgm, katkı modülü içinde bulunur.

Bir örnekle görelim

Adım 1. postgresql anonimleştirici kullanarak bazı örnek veriler oluşturun

create table persons
as
select t.id, anon.dummy_first_name() first_name, anon.dummy_last_name() last_name,
anon.fake_email() email,anon.fake_country() country,anon.fake_city() city
from
(
select id
from generate_series(1,1000000 ) as id
) t ;

Adım 2. email sütununda dizin oluşturun

demodb=# create index ix_email on persons (email);

CREATE INDEX

Adım 3. like operatörü ile SQL sorgusu için planı analiz edin

demodb=# explain analyze select * from persons where email like ‘anna37%’;

Aşağıda gösterildiği gibi, plan email sütunundaki dizini kullanmıyor, oysa bir dizin mevcut

Adım 4. pg_trgm uzantısını ve email sütununda trgm dizin oluşturun

demodb=# create extension pg_trgm ;

demodb=# CREATE INDEX trgm_email_idx ON persons USING gist (email gist_trgm_ops);

Adım 5. Yeni trgm dizin oluşturduktan sonra aynı sorguyu yeni plan ile açıklayın

demodb=# explain analyze select * from persons where email like ‘anna37%’;

Artık yeni oluşturulan trgm dizinini kullanıyor. Ve maliyet 24.532'den 375'e düştü ve sorgu süresi 23 ms'ye 90 ms'den azaldı.