Postgresqlde Explain-Analyze ve Reindex İşlemleri

EXPLAIN

Postgresql veri tabanında toplanan istatistikler, sorgu sonuçlarının daha hızlı ve minimum maliyet ile kullanıcıya gösterilmesi amacıyla kullanılır. Query planner denilen işlem sayesinde bir sql cümlesinin nasıl çalıştırılacağına karar verilir. Bir Sql cümlesinin çalıştırılma adımlarını görmek için EXPLAIN komutu kullanılır. Explain komutu ile sql’in çalıştırılma adımları, sequential veya index scan yapıp yapmadığı ve sorgunun çalıştırılma bilgisi olan cost değeri öğrenilebilir. Çalıştırılacak sql cümlesinin başına EXPLAIN keywordü yazıldığında sql plan görüntülenir.

Maliyet yani cost bilgsinin hesaplanma formülü aşağıdaki gibidir.

COST = (disk read pages * seq_page_cost) + (rows scanned * cpu_tuple_cost).

Disk read pages ve rows scanned değerleri pg_class isimli metadata tablosundan okunur.

Seq_page_cost değeri disk üzerinden okunacak page’lerin tahmini maliyetidir.

Cpu tuple cost ise her tuple’ın işlenmesi için CPU tarafından öngörülen tahmini maliyeti ifade eder.

EXPLAIN komutu aşağıdaki gibi kullanıldığında cost bilgilerini göstermez ;

EXPLAIN (COST FALSE) Select * from table;

EXPLAIN Komut çıktısının formatını (TEXT,XML,JSON) değiştirmek için FORMAT opsiyonu kullanılır.

ANALYZE

Maliyeti doğru hesaplamak ve query planner’ın en optimize biçimde sorgu adımlarını oluşturabilmesi için veri tabanı istatistiklerinin güncel olması gerekmektedir. Veri tabanı istatistik bilgilerinin toplanmasını ANALYZE komutu sağlar. Bu komut manuel olarak çalıştırılabileceği gibi  postgresql veri tabanında autoanalyze opsiyonu da mevcuttur.

Bir tablodaki değişikliklerin threshold değerlerini postgresql.conf parametre dosyasında belirlenir ve auto analyze işlemi veri tabanında belirli aralıklarla tetiklendiğinde bu threshold değerlerine ulamış tabloların istatistiklerinin yeniden toplanmasını sağlar.

Analyze işlemi aşağıdaki gibi yapılır;

ANALYZE ; // Bu komut ile mevcut veri tabanında bütün tablolar için istatistik toplanır.

ANALYZE table_name; // bu komut ile tablo seviyesinde istatistikler toplanır.

ANALYZE table_name(Column list); // bu komut ile tablonun belirli kolonları için istatistik toplanır.

ANALYZE komutu indexleri güncelleme işlemini yapmaz. Sadece tablo ve kolon istatistiklerini günceller. ANALYZE komutu çalıştırıldığında tablo üzerinde bir lock oluşturmaz. Çok güncellenen tablolarda veya bir index oluşturma işleminden hemen önce veya sonra index’in doğru şekilde kullanılması için analyze işlemi kullanılabilir.

Analyze ve vacuum işlemleri arasında bir bağlantı bulunmaktadır. Vacuum işlemi tablolardaki dead tuple’ları temizlediğinden Vacuum işleminden sonra tabloların istatistikleri büyük ölçüde değişmektedir. Query Planner’a bu değişiklikleri bildirmek için vacuum işleminden sonra ANALYZE komutu çalıştırılmalıdır.

Aşağıdaki örnekte bir tablonun vacuum işleminden önceki ve sonrki query planları ile Analyze işlemi yapıldıktan sonraki query planı görüntülenebilir.

create table film_new as select * from film;

ALTER TABLE   film_new SET (AUTOVACUUM_ENABLED=FALSE);

SELECT * from film_new;

UPDATE film_new SET title=title||’NEW’;

EXPLAIN SELECT * FROM film_new;

//”Seq Scan on film_new  (cost=0.00..126.81 rows=1981 width=384)”

SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autoanalyze, last_analyze

FROM pg_stat_all_tables WHERE relname=’film_new’;

VACUUM film_new;

ANALYZE film_new;

“Seq Scan on film_new  (cost=0.00..117.00 rows=1000 width=384)”

REINDEX

Tablolar üzerinde indekslerin efektif biçimde kullanılması için sıklıkla verisi ve yapısı değişen tablolarda REINDEX operasyonunun yapılması gerekir. Bu operasyon ile tablo üzerinde bulunan fakat sorgulama işlemleri esnasında kullanılmayan indexlerin bakım operasyonu gerçekleştirilir.

Bir indexi yeniden yaratmak için aşağıdaki komut kullanılır.

REINDEX INDEX indexname;

Bir tablo üzerindeki bütün indexleri yeniden yaratmak için aşağıdaki komut kullanılır.

REINDEX TABLE tablename;

Bir şemadaki bütün indexleri yeniden yaratmak için aşağıdaki komut kullanılır.

REINDEX SCHEMA schemaname;

Bir veri tabanındaki bütün indexleri yeniden yaratmak için aşağıdaki komut kullanılır.

SELECT
relname AS “table_name”,
pg_size_pretty(pg_table_size(C.oid)) AS “table_size”
FROM
pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN (‘pg_catalog’, ‘information_schema’) AND nspname !~ ‘^pg_toast’ AND relkind IN (‘r’)
ORDER BY pg_table_size(C.oid)
DESC LIMIT 5;