Statistik dan ANALYZE

-- Satu tabel pelanggan dengan distribusi yang sengaja timpang:
-- kota Jakarta menguasai 40% baris, Jayapura hanya 2%.
-- Kolom zona selalu ditentukan oleh kota (korelasi sempurna) --
-- bahan demo statistik ekstensif di bagian akhir chapter.
-- autovacuum dimatikan untuk tabel ini agar demo deterministik:
-- hanya ANALYZE manual yang boleh memperbarui statistiknya.
CREATE TABLE pelanggan (
    id    bigint PRIMARY KEY,
    nama  text NOT NULL,
    kota  text NOT NULL,
    zona  text NOT NULL,
    saldo numeric(12,2) NOT NULL
) WITH (autovacuum_enabled = off);

INSERT INTO pelanggan (id, nama, kota, zona, saldo)
SELECT k.g,
       'Pelanggan ' || k.g,
       k.kota,
       CASE k.kota
           WHEN 'Jakarta'    THEN 'WIB'
           WHEN 'Bandung'    THEN 'WIB'
           WHEN 'Surabaya'   THEN 'WIB'
           WHEN 'Medan'      THEN 'WIB'
           WHEN 'Denpasar'   THEN 'WITA'
           WHEN 'Makassar'   THEN 'WITA'
           WHEN 'Balikpapan' THEN 'WITA'
           ELSE 'WIT'
       END,
       (k.g * 137.75)::numeric(12,2)
FROM (
    SELECT g,
           CASE
               WHEN g % 100 < 40 THEN 'Jakarta'
               WHEN g % 100 < 60 THEN 'Bandung'
               WHEN g % 100 < 75 THEN 'Surabaya'
               WHEN g % 100 < 83 THEN 'Medan'
               WHEN g % 100 < 90 THEN 'Denpasar'
               WHEN g % 100 < 95 THEN 'Makassar'
               WHEN g % 100 < 98 THEN 'Balikpapan'
               ELSE 'Jayapura'
           END AS kota
    FROM generate_series(1, 10000) AS g
) AS k;

CREATE INDEX idx_pelanggan_kota ON pelanggan (kota);

-- Bukti: sebelum ANALYZE, view pg_stats belum punya entri untuk tabel ini.
SELECT count(*) AS baris_statistik_tersedia
FROM pg_stats
WHERE tablename = 'pelanggan';

CREATE TABLE
INSERT 0 10000
CREATE INDEX
 baris_statistik_tersedia 
--------------------------
                        0
(1 row)

-- Sebelum ANALYZE, planner tidak tahu apa pun tentang distribusi kolom kota.
-- Estimasi berikut ditangkap pada keadaan tanpa statistik.
-- Angka biaya dapat sedikit berbeda di mesin Anda.
EXPLAIN
SELECT count(nama)
FROM pelanggan
WHERE kota = 'Jakarta';

-- Bandingkan dengan kenyataannya:
SELECT count(nama) AS baris_jakarta
FROM pelanggan
WHERE kota = 'Jakarta';

-- Bentuk statistik dengan ANALYZE, lalu lihat estimasinya kembali.
ANALYZE pelanggan;

EXPLAIN
SELECT count(nama)
FROM pelanggan
WHERE kota = 'Jakarta';

-- Filter rentang pada kolom numerik memakai sumber estimasi lain: histogram.
EXPLAIN
SELECT count(nama)
FROM pelanggan
WHERE saldo < 700000;

                                       QUERY PLAN                                        
----------------------------------------------------------------------------------------
 Aggregate  (cost=87.14..87.15 rows=1 width=8)
   ->  Bitmap Heap Scan on pelanggan  (cost=4.67..87.02 rows=50 width=32)
         Recheck Cond: (kota = 'Jakarta'::text)
         ->  Bitmap Index Scan on idx_pelanggan_kota  (cost=0.00..4.66 rows=50 width=0)
               Index Cond: (kota = 'Jakarta'::text)
(5 rows)

 baris_jakarta 
---------------
          4000
(1 row)

ANALYZE
                                        QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 Aggregate  (cost=205.28..205.29 rows=1 width=8)
   ->  Bitmap Heap Scan on pelanggan  (cost=51.28..195.28 rows=4000 width=14)
         Recheck Cond: (kota = 'Jakarta'::text)
         ->  Bitmap Index Scan on idx_pelanggan_kota  (cost=0.00..50.28 rows=4000 width=0)
               Index Cond: (kota = 'Jakarta'::text)
(5 rows)

                             QUERY PLAN                              
---------------------------------------------------------------------
 Aggregate  (cost=231.70..231.71 rows=1 width=8)
   ->  Seq Scan on pelanggan  (cost=0.00..219.00 rows=5081 width=14)
         Filter: (saldo < '700000'::numeric)
(3 rows)

# ANALYZE VERBOSE mencetak laporan pengambilan sampel lewat pesan INFO.
# Redireksi 2>&1 diperlukan karena psql mengirim pesan INFO ke stderr.
psql -X -c "ANALYZE (VERBOSE) pelanggan;" 2>&1

INFO:  analyzing "public.pelanggan"
INFO:  "pelanggan": scanned 94 of 94 pages, containing 10000 live rows and 0 dead rows; 10000 rows in sample, 10000 estimated total rows
ANALYZE

-- Isi statistik kolom kota dan zona: daftar nilai terumum beserta frekuensinya.
SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'pelanggan' AND attname IN ('kota', 'zona')
ORDER BY attname;

-- Kolom id dan saldo: semua nilai unik, statistiknya berbentuk histogram.
SELECT attname,
       n_distinct,
       array_length(histogram_bounds, 1) AS jumlah_batas,
       (regexp_split_to_array(btrim(histogram_bounds::text, '{}'), ','))[1:3] AS tiga_batas_pertama
FROM pg_stats
WHERE tablename = 'pelanggan' AND attname IN ('id', 'saldo')
ORDER BY attname;

 attname | n_distinct |                            most_common_vals                            |            most_common_freqs            
---------+------------+------------------------------------------------------------------------+-----------------------------------------
 kota    |          8 | {Jakarta,Bandung,Surabaya,Medan,Denpasar,Makassar,Balikpapan,Jayapura} | {0.4,0.2,0.15,0.08,0.07,0.05,0.03,0.02}
 zona    |          3 | {WIB,WITA,WIT}                                                         | {0.83,0.15,0.02}
(2 rows)

 attname | n_distinct | jumlah_batas |     tiga_batas_pertama     
---------+------------+--------------+----------------------------
 id      |         -1 |          101 | {1,100,200}
 saldo   |         -1 |          101 | {137.75,13775.00,27550.00}
(2 rows)

-- Naikkan target statistik kolom saldo dari default 100 menjadi 1000.
ALTER TABLE pelanggan ALTER COLUMN saldo SET STATISTICS 1000;

-- ANALYZE ulang mewujudkan target baru menjadi histogram yang lebih rapat.
ANALYZE pelanggan;

SELECT attname,
       array_length(histogram_bounds, 1) AS jumlah_batas
FROM pg_stats
WHERE tablename = 'pelanggan' AND attname = 'saldo';

ALTER TABLE
ANALYZE
 attname | jumlah_batas 
---------+--------------
 saldo   |         1001
(1 row)

-- Dua filter pada kolom yang berkorelasi: kota selalu menentukan zona.
-- Tanpa statistik ekstensif, planner mengalikan selectivity kedua kolom
-- seolah-olah keduanya saling bebas.
EXPLAIN
SELECT count(nama)
FROM pelanggan
WHERE kota = 'Jayapura' AND zona = 'WIT';

-- Bandingkan dengan kenyataannya:
SELECT count(nama) AS baris_jayapura_wit
FROM pelanggan
WHERE kota = 'Jayapura' AND zona = 'WIT';

-- Statistik ekstensif: catat ketergantungan fungsional antar kolom.
CREATE STATISTICS st_kota_zona (dependencies) ON kota, zona FROM pelanggan;

ANALYZE pelanggan;

-- Estimasi kini memakai korelasi antar kolom, bukan asumsi independen.
EXPLAIN
SELECT count(nama)
FROM pelanggan
WHERE kota = 'Jayapura' AND zona = 'WIT';

-- Isi statistik ekstensif: ketergantungan tercatat sebagai tingkat kepercayaan.
SELECT statistics_name, dependencies
FROM pg_stats_ext
WHERE statistics_name = 'st_kota_zona';

                                       QUERY PLAN                                        
-----------------------------------------------------------------------------------------
 Aggregate  (cost=102.80..102.81 rows=1 width=8)
   ->  Bitmap Heap Scan on pelanggan  (cost=5.79..102.79 rows=4 width=14)
         Recheck Cond: (kota = 'Jayapura'::text)
         Filter: (zona = 'WIT'::text)
         ->  Bitmap Index Scan on idx_pelanggan_kota  (cost=0.00..5.79 rows=200 width=0)
               Index Cond: (kota = 'Jayapura'::text)
(6 rows)

 baris_jayapura_wit 
--------------------
                200
(1 row)

CREATE STATISTICS
ANALYZE
                                       QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 Aggregate  (cost=103.33..103.34 rows=1 width=8)
   ->  Bitmap Heap Scan on pelanggan  (cost=5.83..102.83 rows=200 width=14)
         Recheck Cond: (kota = 'Jayapura'::text)
         Filter: (zona = 'WIT'::text)
         ->  Bitmap Index Scan on idx_pelanggan_kota  (cost=0.00..5.79 rows=200 width=0)
               Index Cond: (kota = 'Jayapura'::text)
(6 rows)

 statistics_name |               dependencies               
----------------+------------------------------------------
 st_kota_zona    | {"3 => 4": 1.000000, "4 => 3": 0.020000}
(1 row)

-- 2.000 pelanggan baru, semuanya dari Jakarta (promo besar-besaran).
-- Statistik tabel sengaja tidak diperbarui setelahnya.
INSERT INTO pelanggan (id, nama, kota, zona, saldo)
SELECT 10000 + g,
       'Pelanggan Promo ' || g,
       'Jakarta',
       'WIB',
       ((10000 + g) * 137.75)::numeric(12,2)
FROM generate_series(1, 2000) AS g;

-- Counter statistik di-flush secara asinkron; paksa flush sesi ini
-- agar pg_stat_user_tables menampilkan keadaan mutakhir.
SELECT pg_stat_force_next_flush();

-- Tiga versi "berapa baris tabel ini?" -- ketiganya berbeda peran:
-- statistik planner, counter maintenance, dan kenyataan di tabel.
SELECT reltuples::bigint AS baris_menurut_statistik
FROM pg_class
WHERE relname = 'pelanggan';

SELECT n_live_tup AS baris_counter,
       n_mod_since_analyze AS modifikasi_sejak_analyze,
       round(50 + 0.1 * n_live_tup) AS ambang_autoanalyze,
       last_autoanalyze IS NULL AS belum_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'pelanggan';

SELECT count(*) AS baris_sebenarnya
FROM pelanggan;

INSERT 0 2000
 pg_stat_force_next_flush 
--------------------------
 
(1 row)

 baris_menurut_statistik 
-------------------------
                   10000
(1 row)

 baris_counter | modifikasi_sejak_analyze | ambang_autoanalyze | belum_autoanalyze 
---------------+--------------------------+--------------------+-------------------
         12000 |                     2000 |               1250 | t
(1 row)

 baris_sebenarnya 
------------------
            12000
(1 row)