PostgreSQL bu işlemleri hızlandırmak için ne yapabilir ve genel olarak sorunu anlamak ve ikinci olarak daha iyi PostgreSQL veritabanı performansı elde etmek için neler yapılabilir....
Örnek veri oluşturma
Bu blog yazısında çoğunlukla Gist ve GIN indeksleme hakkında bilgi edineceksiniz. Her iki indeks türü de ILIKE'nin yanı sıra LIKE'yi de işleyebilir. Bu dizin türleri eşit derecede verimli değildir, bu nedenle konuyu araştırmak ve neyin en iyi olduğunu bulmak mantıklıdır.
Başlamadan önce bazı örnek veriler oluşturdum. Web'de örnek veri aramaktan kaçınmak için bazı veriler üretmeye karar verdim. Burada basit bir md5 hash yeterli olduğunu kanıtlamak için yeterlidir.
test=# CREATE TABLE t_hash AS SELECT id, md5(id::text)
FROM generate_series(1, 50000000) AS id;
SELECT 50000000
test=# VACUUM ANALYZE;
VACUUMVerilere bir göz atalım. Burada elimizde 50 milyon kimlik var ve bunların karmaları var. Aşağıdaki liste, verilerin genel olarak nasıl göründüğünü göstermektedir:
test=# SELECT * FROM t_hash LIMIT 10;
id | md5
----+----------------------------------
1 | c4ca4238a0b923820dcc509a6f75849b
2 | c81e728d9d4c2f636f067f89cc14862c
3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
4 | a87ff679a2f3e71d9181a67b7542122c
5 | e4da3b7fbbce2345d7772b0674a318d5
6 | 1679091c5a880faf6fb5e6087eb1b2dc
7 | 8f14e45fceea167a5a36dedd4bea2543
8 | c9f0f895fb98ab9159f51fd0297e236d
9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
10 | d3d9446802a44259755d38e6d163e820
(10 rows)PostgreSQL'de basit LIKE sorguları çalıştırma
Dikkatimizi LIKE olarak değiştirelim: Aşağıdaki sorgu, verilerde bulunan bir alt dizeyi yalnızca bir kez seçer. Yüzde sembolünün sadece sonunda değil, aynı zamanda desenin başında olduğunu unutmayın:
test=# timing
Timing is on.
test=# SELECT * FROM t_hash WHERE md5 LIKE '%e2345679a%';
id | md5
----------+----------------------------------
37211731 | dadb4b54e2345679a8861ab52e4128ea
(1 row)
Time: 4767.415 ms (00:04.767)İMac'imde, sorgunun tamamlanması 4.7 saniye sürüyor. Tüm uygulamaların% 90 + 'sında bu zaten çok uzun. Kullanıcı deneyimi zaten acı çekecek ve bunun gibi uzun süren bir sorgunun zaten sunucunuzdaki yükü oldukça önemli ölçüde arttırma ihtimali vardır.
Kaputun altında neler olup bittiğini görmek için SQL ifadesinin yürütme planını dahil etmeye karar verdim:
test=# explain SELECT * FROM t_hash WHERE md5 LIKE '%e2345679a%';
QUERY PLAN
------------------------------------------------------------------------------
Gather (cost=1000.00..678583.88 rows=5000 width=37)
Workers Planned: 2
-> Parallel Seq Scan on t_hash (cost=0.00..677083.88 rows=2083 width=37)
Filter: (md5 ~~ '%e2345679a%'::text)
(4 rows)
Time: 11.531 msTablonun boyutu nedeniyle PostgreSQL sorgu optimizer paralel bir sorgu için gidecek. Bu temelde iyi bir şey çünkü yürütme süresi yarı yarıya azaldı. Ancak: Bu aynı zamanda tek bir satır döndüren bu sorguyu yanıtlamak için iki CPU çekirdeğini kolayca feda ettiğimiz anlamına gelir.
Kötü performansın nedeni, tablonun aslında oldukça büyük olması ve veritabanının isteği işlemek için baştan sona okuması gerektiğidir:
test=# dt+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+--------+-------+-------+---------+-------------
public | t_hash | table | hs | 3256 MB |
(1 row)Sadece bir tanesini almak için 3.2 GB okumak artık hiç verimli değil.
Peki bu sorunu çözmek için ne yapabiliriz?
pg_trgm: Gelişmiş dizinleme
Neyse ki PostgreSQL, desen eşleştirme alanında çok fazla hile yapabilen bir modül sunuyor. Pg_trgm uzantısı, bulanık aramaya yardımcı olmanın bir yolu olan “trigramlar” uygular. Uzantı, PostgreSQL katkı paketinin bir parçasıdır ve bu nedenle sistemlerin büyük çoğunluğunda bulunmalıdır:
test=# CREATE EXTENSION pg_trgm;
CREATE EXTENSION
Time: 77.216 msGördüğünüz gibi uzantının etkinleştirilmesi kolaydır. Şimdi ortaya çıkan doğal soru şudur: Trigram nedir? Bir bakalım ve görelim:
test=# SELECT show_trgm('dadb4b54e2345679a8861ab52e4128ea');
show_trgm
---------------------------------------------------------------------------------------------------------------------------------------------
{" d"," da",128,1ab,234,28e,2e4,345,412,456,4b5,4e2,52e,54e,567,61a,679,79a,861,886,8ea,9a8,a88,ab5,adb,b4b,b52,b54,dad,db4,e23,e41,"ea "}
(1 row)Gözleyebileceğiniz şey, bir üçgenin kayan 3 karakterlik bir pencere gibi olmasıdır. Tüm bu jetonlar daha sonra göreceğiniz gibi dizinde görünecektir.
LIKE dizinini oluşturmak için pg_trgm modülü iki PostgreSQL dizin türünü destekler: Gist ve GIN. Her iki seçenek de değerlendirilecektir.
Gist: Gist ile trigram indeksleri dağıtma
PostgreSQL'de bulanık aramayı hızlandırmak için birçok insanın yaptığı şey, Gist dizinlerini kullanmaktır. Bu tür bir dizin nasıl dağıtılabilir:
test=# CREATE INDEX idx_gist ON t_hash USING gist (md5 gist_trgm_ops);
CREATE INDEX
Time: 2383678.930 ms (39:43.679)Gördüğünüz şey, dizinin oluşturulması için biraz zamana ihtiyaç duyması. Belirtilmesi gereken, daha yüksek bakım_çalışma_mem ayarlarının bile işlemi hızlandırmayacağıdır. 4 GB maintenance_work_mem ile bile işlem 40 dakika sürecektir.
Dikkate değer olan şey, endeksin gerçekten büyük olması:
test=# di+
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+----------+-------+-------+--------+---------+-------------
public | idx_gist | index | hs | t_hash | 8782 MB |
(1 row)Tablonun sadece 3,5 GB olduğunu unutmayın - endeks 2,5 kat daha büyüktür.
Ancak, dizinler her zaman işleri daha hızlı hale getirecek, değil mi? Aslında hayır…
test=# SELECT * FROM t_hash WHERE md5 LIKE '%e2345679a%';
id | md5
----------+----------------------------------
37211731 | dadb4b54e2345679a8861ab52e4128ea
(1 row)
Time: 105506.064 ms (01:45.506)Sorguyu gerçekten “optimize ettik” mi? 4,7 saniye yerine PostgreSQL'in işi yapmak için yaklaşık 2 dakikaya ihtiyacı var. Neden böyle? İcra planının söylediklerine bir göz atalım:
test=# explain SELECT * FROM t_hash WHERE md5 LIKE '%e2345679a%';
QUERY PLAN
----------------------------------------------------------------------------
Bitmap Heap Scan on t_hash (cost=495.30..18812.90 rows=5000 width=37)
Recheck Cond: (md5 ~~ '%e2345679a%'::text)
-> Bitmap Index Scan on idx_gist (cost=0.00..494.05 rows=5000 width=0)
Index Cond: (md5 ~~ '%e2345679a%'::text)
(4 rows)
Time: 13.433 msPostgreSQL optimizer bir “Bitmap Dizin Taraması” yapmaya karar verdi. Yani doğrudan bir indeks taraması daha iyi olabilir mi?
test=# SET enable_bitmapscan TO off;
SET
Time: 11.302 ms
test=# explain analyze SELECT * FROM t_hash WHERE md5 LIKE '%e2345679a%';
QUERY PLAN
-----------------------------------------------------------------------------
Index Scan using idx_gist on t_hash (cost=0.55..20428.04 rows=5000 width=37)
(actual time=13750.850..99070.510 rows=1 loops=1)
Index Cond: (md5 ~~ '%e2345679a%'::text)
Planning Time: 0.074 ms
Execution Time: 99070.618 ms
(4 rows)
Time: 99072.657 ms (01:39.073)Aslında sorgu hala korkunç yürütme sürelerini gösterecek.
Kısacası: Gist endeksi burada kullanılacak doğru şey olmayabilir. Oluşturulması uzun sürer, büyüktür, sıralı bir taramadan çok daha yavaştır.
Desen eşleşmesi için GIN dizinlerini kullanma
Neyse ki pg_trgm uzantıları işi tamamlamak için ikinci bir operatör sınıfı sunar. GIN dizinleri genellikle PostgreSQL Tam Metin araması (FTS) için kullanılır. LIKE ve ILIKE durumunda da kazanıp kazanamayacağımızı görelim mi? Bunu yapmadan önce mevcut bağlantıyı sıfırlıyor ve eski dizini bırakıyoruz:
test=# DISCARD ALL;
DISCARD ALL
Time: 12.000 ms
test=# DROP INDEX idx_gist;
DROP INDEX
Time: 3123.336 ms (00:03.123)Bir sonraki adımda yeni bir dizin oluşturulur:
test=# CREATE INDEX idx_gin ON t_hash USING gin (md5 gin_trgm_ops);
CREATE INDEX
Time: 698063.038 ms (11:38.063)Makinemde Gist dizini oluşturma işleminden çok ama aslında çok daha hızlı olan 11 dakika sürüyor. Ancak, dizin oluşturma yalnızca bir kez gerçekleşir, bu nedenle bu durumda çok fazla endişelenmemeliyiz. Genellikle daha önemli olan (genellikle) sorgu yürütme süresidir:
test=# explain analyze SELECT * FROM t_hash WHERE md5 LIKE '%e2345679a%';
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on t_hash (cost=2270.75..20588.36 rows=5000 width=37)
(actual time=74.592..74.593 rows=1 loops=1)
Recheck Cond: (md5 ~~ '%e2345679a%'::text)
Heap Blocks: exact=1
-> Bitmap Index Scan on idx_gin (cost=0.00..2269.50 rows=5000 width=0)
(actual time=74.584..74.584 rows=1 loops=1)
Index Cond: (md5 ~~ '%e2345679a%'::text)
Planning Time: 0.066 ms
Execution Time: 74.665 ms
(7 rows)
Time: 75.031 msVay be, sorguyu sırasıyla 4.7 saniye yerine 1 dakika 45 saniye yerine 75 milisaniyede çalıştırabiliriz. Bu ileriye doğru büyük bir sıçrama. İnsan için küçük bir dizin - veritabanı performansı için dev bir adım.
Beklendiği gibi sorgu tam olarak bir satır döndürür:
test=# SELECT * FROM t_hash WHERE md5 LIKE '%e2345679a%';
id | md5
----------+----------------------------------
37211731 | dadb4b54e2345679a8861ab52e4128ea
(1 row)
Time: 74.487 msŞimdiye kadar gördüğünüz şey, GIN endeksinin sorunu çözmesidir. Ancak, yine de burada ikinci bir dizine ihtiyacınız olabilir. GIN “=” operatörünü hızlandırmaz. Dolayısıyla, normal bir arama arıyorsanız, bir sonraki örnekte gösterildiği gibi ikinci bir dizine ihtiyacınız olacaktır:
test=# CREATE INDEX idx_btree ON t_hash (md5);
CREATE INDEX
Time: 274778.776 ms (04:34.779)
test=# di+
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+-----------+-------+-------+--------+---------+-------------
public | idx_btree | index | hs | t_hash | 2816 MB |
public | idx_gist | index | hs | t_hash | 2807 MB |
(2 rows)Normal karşılaştırmaları hızlandırmak için bir ağaç gereklidir. Bunun için tek başına bir GIN endeksi yeterli olmayacaktır:
test=# SELECT * FROM t_hash WHERE md5 = 'dadb4b54e2345679a8861ab52e4128ea';
id | md5
----------+----------------------------------
37211731 | dadb4b54e2345679a8861ab52e4128ea
(1 row)
Time: 0.379 msSonuç
PostgreSQL gerçekten güçlü indeksleme stratejileri sunar. Keşfedilecek btree dizinlerinden çok daha fazlası var. Gist ve GIN'in de güçlü yanları var. GIN özellikle her türlü tam metin işlemi için yararlıdır, Gist ise geometrik veriler (GIS) için idealdir.
Kaynak : https://www.cybertec-postgresql.com/en/postgresql-more-performance-for-like-and-ilike-statements/
