Sorguların İyileştirilmesi
Düzeltme Öner

Sorguların İyileştirilmesi

Yavaş Sorguların Belirlenmesi

  • postgresql.conf dosyasında
    • logging_collector = on
    • log_directory = ‘pg_log’
    • #log_statement = ‘all’
    • log_min_duration_statement = 1000
    • log_line_prefix = ‘%m’

Yavaş sorgu logu örnekleri:

> LOG:  duration: 1117.957 ms  statement: commit;
> LOG:  duration: 1377.860 ms  statement: UPDATE sessions SET lastaccess=1515423465 WHERE userid='14' AND sessionid='667a73c4605b46bec3f3aca713b52134'
> LOG:  duration: 2037.666 ms  statement: insert into trends (itemid,clock,num,value_min,value_avg,value_max) values
...

İndeksler

  • Okuma performansını arttırır
  • Yazma performansını düşürür
  • Harcanan disk alanını arttırır
  • Bir indeksin olması != kullanılması
  • İndeks denemeleri gerçek ve tüm veri üzerinde yapılmalı
  • Index-only tarama ve görünürlük haritası

İndeks Türleri

  • Tek kolon
  • Çok-kolon: Belirli bir sorgu yapısı için oluşturulur. Esnek değil. Sadece ilk kolonu tek kolon kullanılabiliyor.
  • Unique: İndekslenen kolonda birden fazla aynı değer olmayacağı garantili. Daha hızlı çalışıyor.
  • Kısmi: Tablonun belirli bir kısmının indekslenmesi. Hızlıdırlar, az yer kaplarlar, yazma performansını az düşürürler.
  • Örtülü (Implicit): Birincil/yabancı anahtar ilişkileri için otomatik oluşturulurlar.
  • B-Tree: Öntanımlı. Her derde deva. Bir değerin bir karşılığı olacak biçimde optimize.
  • Hash: Eşitlik kıyaslamaları. B-Tree’den daha küçük. Çökerse elle oluşturulması gerekiyor.
  • GIN: Tam metinde arama + diziler için.
  • GiST: Geometrik veri tipleri + tam metinde arama için.

İndekslerden Ne Zaman Kaçınmalı?

  • Az tetiklenen sorgular için özel indeks yapmayarak
  • Küçük tablolarda
  • Sık ve büyük güncelleme ya da veri girişi olan tablolarda
  • Çoğu verinin NULL olduğu kolonlarda
  • Kolon yapısı ile sık oynanan kolonlarda

İndeks Kullanım Bilgileri

  • pg_index: indeks kullanan aramalar
  • pg_stat_user_tables: indeks kullanmayan aramalar
  • pg_index
  • Önemli kolonlar:
    • idx_scan: Index Scan yapılma sayısı
    • idx_tup_read: İndeks ile okunan kayıt sayısı
    • idx_tup_fetch: İndeks ile alınan kayıt sayısı

İndeksler ve kullanım durumları nedir?

SELECT * FROM pg_stat_user_indexes;

İndeks kullanım durumu, index ve tablo boyutlarıyla beraber:

SELECT t.tablename, indexname, c.reltuples AS num_rows,
 pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
 pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
 CASE WHEN indisunique THEN 'Y'
 ELSE 'N'
 END AS UNIQUE,
 idx_scan AS number_of_scans, idx_tup_read AS tuples_read, idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
 ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
  JOIN pg_class c ON c.oid = x.indrelid
  JOIN pg_class ipg ON ipg.oid = x.indexrelid
  JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
 AS foo
 ON t.tablename = foo.ctablename
WHERE t.schemaname='public' ORDER BY 1,2;

Seq tarama, indeks taramadan fazla mı?

SELECT schemaname, relname, seq_scan-idx_scan AS too_much_seq, case when seq_scan-idx_scan>0
  THEN 'Missing Index?' ELSE 'OK' END,
  pg_relation_size(format('%I.%I', schemaname, relname)::regclass) AS rel_size, seq_scan, idx_scan
 FROM pg_stat_user_tables
 WHERE pg_relation_size(format('%I.%I', schemaname, relname)::regclass)>80000
 ORDER BY too_much_seq DESC;

Çift (aynı) indeks var mı?

SELECT pg_size_pretty(SUM(pg_relation_size(idx))::BIGINT) AS SIZE,
       (array_agg(idx))[1] AS idx1, (array_agg(idx))[2] AS idx2,
       (array_agg(idx))[3] AS idx3, (array_agg(idx))[4] AS idx4
FROM (
    SELECT indexrelid::regclass AS idx, (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'||
                                         COALESCE(indexprs::text,'')||E'\n' || COALESCE(indpred::text,'')) AS KEY
    FROM pg_index) sub
GROUP BY KEY HAVING COUNT(*)>1
ORDER BY SUM(pg_relation_size(idx)) DESC;

Tablo İstatistikleri

  • Sorgu planlamasında kullanılıyor.
  • pg_statistic kataloğunda tutuluyor.
  • İstatistikler canlı güncellenmiyor.
  • ANALYZE komutu tarafından oluşturuluyor.
  • autovacuum’un bir parçası olarak otomatik çalıştırılıyor.
  • Güncel olsa bile tüm istatistikler yaklaşık veri içerir.
  • Büyük veri değişikliklerinden sonra elle ANALYZE çalıştırmak yararlı.

Sorgu Planlayıcısı

  • Bir sorguyu gerçekleştirmenin birçok yöntemi var
  • Sorgu planlayıcısı en verimli sorguya karar veriyor
  • Planlama için tablo istatistiklerini kullanıyor
  • Performansı doğrudan etkiliyor
  • Hayaller Paris gerçekler Eminönü olursa, elle müdahale mümkün