PostgreSQL Query-optimalisatie: Van Traag naar Sub-Milliseconde | Akousa

                                    QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=1 width=128)
   Index Cond: (customer_id = 42)

                                    QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=1 width=128)
                                                    (actual time=0.021..0.023 rows=1 loops=1)
   Index Cond: (customer_id = 42)
 Planning Time: 0.085 ms
 Execution Time: 0.041 ms

                                    QUERY PLAN
---------------------------------------------------------------------------
 Hash Join  (cost=425.00..1893.25 rows=2150 width=48)
            (actual time=2.341..15.892 rows=2347 loops=1)
   Hash Cond: (o.customer_id = c.id)
   Buffers: shared hit=312 read=45
   ->  Bitmap Heap Scan on orders o  (cost=12.50..1450.75 rows=2150 width=32)
                                     (actual time=0.512..12.105 rows=2347 loops=1)
         Recheck Cond: (created_at > '2026-01-01'::date)
         Filter: (status = 'completed')
         Rows Removed by Filter: 893
         Heap Blocks: exact=189
         Buffers: shared hit=195 read=45
         ->  Bitmap Index Scan on idx_orders_created_at  (cost=0.00..11.96 rows=3043 width=0)
                                                         (actual time=0.287..0.287 rows=3240 loops=1)
               Index Cond: (created_at > '2026-01-01'::date)
               Buffers: shared hit=12
   ->  Hash  (cost=287.50..287.50 rows=10000 width=24)
             (actual time=1.753..1.753 rows=10000 loops=1)
         Buffers: shared hit=117
         ->  Seq Scan on customers c  (cost=0.00..287.50 rows=10000 width=24)
                                      (actual time=0.008..0.892 rows=10000 loops=1)
               Buffers: shared hit=117
 Planning Time: 0.215 ms
 Execution Time: 16.147 ms

Bitmap Heap Scan on orders  (cost=45.00..1250.00 rows=5000 width=128)
  Recheck Cond: (created_at > '2026-01-01')
  ->  Bitmap Index Scan on idx_orders_created_at  (cost=0.00..43.75 rows=5000 width=0)
        Index Cond: (created_at > '2026-01-01')

Bitmap Heap Scan on orders
  Recheck Cond: ((status = 'completed') AND (created_at > '2026-01-01'))
  ->  BitmapAnd
        ->  Bitmap Index Scan on idx_orders_status
              Index Cond: (status = 'completed')
        ->  Bitmap Index Scan on idx_orders_created_at
              Index Cond: (created_at > '2026-01-01')

sql

-- Query: WHERE status = 'active' AND created_at > '2026-01-01' ORDER BY total DESC
 
-- Goed: equality eerst, dan range, dan sort
CREATE INDEX idx_orders_optimized ON orders (status, created_at, total);
 
-- Slecht: range voor equality
CREATE INDEX idx_orders_bad ON orders (created_at, status, total);

sql

-- Nog een veelvoorkomend patroon: soft deletes
CREATE INDEX idx_users_active_email ON users (email)
WHERE deleted_at IS NULL;
 
-- Unieke constraint alleen op actieve records
CREATE UNIQUE INDEX idx_users_unique_email ON users (email)
WHERE deleted_at IS NULL;

sql

-- JSONB containment queries
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
 
-- Nu is dit snel:
SELECT * FROM products WHERE metadata @> '{"color": "red"}';
 
-- Full-text search
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', title || ' ' || body));

sql

-- IP-range lookups
CREATE INDEX idx_ip_ranges ON ip_blocks USING GiST (ip_range);
 
SELECT * FROM ip_blocks WHERE ip_range @> '192.168.1.50'::inet;
 
-- Temporele ranges (boekingsconflicten)
CREATE INDEX idx_bookings_period ON bookings USING GiST (
  tstzrange(start_time, end_time)
);
 
-- Vind overlappende boekingen:
SELECT * FROM bookings
WHERE tstzrange(start_time, end_time) && tstzrange('2026-03-01 10:00', '2026-03-01 12:00');

sql

-- Goed voor equality-only lookups op grote tekstkolommen
CREATE INDEX idx_sessions_token ON sessions USING HASH (session_token);
 
-- Dit is snel:
SELECT * FROM sessions WHERE session_token = 'abc123def456...';
 
-- Dit KAN de hash-index NIET gebruiken:
SELECT * FROM sessions WHERE session_token LIKE 'abc%';

sql

-- Vind ongebruikte indexen
SELECT
  schemaname, tablename, indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelid NOT IN (
    SELECT conindid FROM pg_constraint
    WHERE contype IN ('p', 'u')  -- sluit primary key en unique constraints uit
  )
ORDER BY pg_relation_size(indexrelid) DESC;

sql

-- Vind dubbele indexen (dezelfde kolommen, dezelfde volgorde)
SELECT
  a.indexrelid::regclass AS index1,
  b.indexrelid::regclass AS index2,
  a.indrelid::regclass AS table_name
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
  AND a.indexrelid < b.indexrelid
  AND a.indkey = b.indkey;

python

# Python / SQLAlchemy — de klassieke val
orders = session.query(Order).filter_by(status='pending').all()
 
for order in orders:
    # Dit triggert een aparte query voor ELKE bestelling
    print(f"Customer: {order.customer.name}")

sql

-- Query 1: Haal alle lopende bestellingen op
SELECT * FROM orders WHERE status = 'pending';
 
-- Query 2: Haal klant op voor bestelling 1
SELECT * FROM customers WHERE id = 101;
 
-- Query 3: Haal klant op voor bestelling 2
SELECT * FROM customers WHERE id = 102;
 
-- ...Query 502: Haal klant op voor bestelling 500
SELECT * FROM customers WHERE id = 600;

sql

-- Log alle queries die meer dan 0ms duren (dus alle queries)
SET log_min_duration_statement = 0;
 
-- Beter voor productie: log alleen trage queries
ALTER SYSTEM SET log_min_duration_statement = 50;  -- 50ms drempel
SELECT pg_reload_conf();

sql

-- Schakel auto_explain in voor trage queries
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 100;  -- 100ms
SET auto_explain.log_nested_statements = ON;
SET auto_explain.log_analyze = ON;

python

# SQLAlchemy — joinedload haalt klanten op in dezelfde query
from sqlalchemy.orm import joinedload
 
orders = (
    session.query(Order)
    .options(joinedload(Order.customer))
    .filter_by(status='pending')
    .all()
)

sql

-- Aanpak 1: Enkele JOIN (kan dubbele data produceren bij one-to-many)
SELECT o.*, i.product_name, i.quantity
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'pending';
 
-- Aanpak 2: Twee queries (minder datatransfer als orders veel kolommen hebben)
SELECT * FROM orders WHERE status = 'pending';
-- applicatie verzamelt order_ids = [1, 2, 3, ...]
SELECT * FROM order_items WHERE order_id = ANY($1);

sql

-- Subquery in WHERE — vaak prima, soms verschrikkelijk
SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE country = 'US'
);
 
-- JOIN — meestal de beste keuze
SELECT o.* FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'US';
 
-- CTE — gematerialiseerd in PG 11 en lager, geoptimaliseerd in PG 12+
WITH us_customers AS (
  SELECT id FROM customers WHERE country = 'US'
)
SELECT o.* FROM orders o
JOIN us_customers uc ON uc.id = o.customer_id;

sql

-- EXISTS — stopt bij de eerste match, geweldig voor bestaanscontrole
SELECT * FROM orders o
WHERE EXISTS (
  SELECT 1 FROM returns r WHERE r.order_id = o.id
);
 
-- IN met subquery — bouwt eerst de volledige resultaatset
SELECT * FROM orders
WHERE id IN (SELECT order_id FROM returns);
 
-- JOIN — kan duplicaten produceren als returns meerdere rijen per bestelling heeft
SELECT DISTINCT o.* FROM orders o
JOIN returns r ON r.order_id = o.id;

sql

-- Slecht: haalt alle 25 kolommen op inclusief een TEXT-kolom met kilobytes aan data
SELECT * FROM products WHERE category = 'electronics';
 
-- Goed: haalt alleen op wat je nodig hebt
SELECT id, name, price FROM products WHERE category = 'electronics';

sql

-- Traag: gecorreleerde subquery draait eenmaal per rij
SELECT
  o.*,
  (SELECT COUNT(*) FROM order_items i WHERE i.order_id = o.id) AS item_count,
  (SELECT SUM(amount) FROM payments p WHERE p.order_id = o.id) AS total_paid
FROM orders o
WHERE o.status = 'completed';
 
-- Snel: window functions berekenen in een enkele pass
SELECT
  o.*,
  COUNT(i.id) OVER (PARTITION BY o.id) AS item_count,
  SUM(p.amount) OVER (PARTITION BY o.id) AS total_paid
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
LEFT JOIN payments p ON p.order_id = o.id
WHERE o.status = 'completed';

sql

-- Traag: gecorreleerde subquery
SELECT * FROM notifications n
WHERE n.created_at = (
  SELECT MAX(created_at) FROM notifications
  WHERE user_id = n.user_id
);
 
-- Snel: DISTINCT ON (PostgreSQL-specifiek, extreem nuttig)
SELECT DISTINCT ON (user_id) *
FROM notifications
ORDER BY user_id, created_at DESC;
 
-- Ook snel: ROW_NUMBER window function
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM notifications
) sub
WHERE rn = 1;

sql

-- Pagina 1: snel
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 0;
 
-- Pagina 500: traag — PostgreSQL moet 9.980 rijen scannen en verwerpen
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 9980;
 
-- Pagina 5000: heel traag — scant 99.980 rijen om er 20 terug te geven
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 99980;

sql

-- Analyseer een enkele tabel
ANALYZE orders;
 
-- Analyseer de hele database
ANALYZE;
 
-- Bekijk huidige statistieken voor een kolom
SELECT
  tablename, attname, n_distinct, most_common_vals, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

sql

-- Controleer autovacuum-status
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

sql

-- Agressievere autovacuum voor write-heavy tabellen
ALTER TABLE sessions SET (
  autovacuum_vacuum_scale_factor = 0.01,     -- vacuum bij 1% dode tuples (standaard 20%)
  autovacuum_analyze_scale_factor = 0.005,   -- analyze bij 0,5% wijzigingen (standaard 10%)
  autovacuum_vacuum_cost_delay = 2           -- snellere vacuum (standaard 2ms in nieuwere PG)
);

sql

-- Schat tabelbloat in (vereenvoudigde versie)
SELECT
  current_database(),
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
  pg_size_pretty(
    pg_total_relation_size(schemaname || '.' || tablename) -
    pg_relation_size(schemaname || '.' || tablename)
  ) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;

ini

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
 
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
 
; Pool mode
pool_mode = transaction
 
; Pool sizing
default_pool_size = 20
max_client_conn = 500
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
 
; Timeouts
server_idle_timeout = 300
client_idle_timeout = 0
query_timeout = 30

javascript

const pool = new Pool({
  host: "127.0.0.1",
  port: 6432, // PgBouncer-poort, niet PostgreSQL-poort
  database: "mydb",
  max: 20, // komt overeen met PgBouncer default_pool_size
  statement_timeout: 30000,
  // Schakel prepared statements uit voor PgBouncer transaction mode
  prepare: false,
});

sql

-- Top 10 queries op totale uitvoeringstijd
SELECT
  queryid,
  calls,
  round(total_exec_time::numeric, 2) AS total_time_ms,
  round(mean_exec_time::numeric, 2) AS avg_time_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

sql

-- Welke indexen bestaan er op deze tabel?
SELECT
  indexname,
  indexdef,
  idx_scan,
  idx_tup_read,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_indexes ON pg_stat_user_indexes.indexrelname = pg_indexes.indexname
WHERE tablename = 'orders'
ORDER BY idx_scan DESC;

sql

-- Maak de index concurrent aan (lockt de tabel niet)
CREATE INDEX CONCURRENTLY idx_orders_customer_status
ON orders (customer_id, status)
WHERE status IN ('pending', 'processing');
 
-- Verifieer dat de index wordt gebruikt
EXPLAIN (ANALYZE, BUFFERS) <de trage query>;

sql

-- work_mem: geheugen voor sorts en hash joins (per operatie, niet per verbinding)
-- Standaard is 4MB, wat te laag is voor complexe queries
SET work_mem = '64MB';  -- probeer dit en voer EXPLAIN ANALYZE opnieuw uit
 
-- effective_cache_size: vertelt de planner hoeveel schijfcache te verwachten
-- Zet op ~75% van totaal RAM
SHOW effective_cache_size;
 
-- random_page_cost: verhouding van random I/O tot sequentiële I/O
-- Standaard is 4.0, zet op 1.1-1.5 voor SSD's
SHOW random_page_cost;

sql

-- Reset pg_stat_statements om verse data te zien na je wijzigingen
SELECT pg_stat_statements_reset();
 
-- Controleer na een uur/dag
SELECT
  queryid,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_time_ms,
  round(max_exec_time::numeric, 2) AS max_time_ms,
  query
FROM pg_stat_statements
WHERE query LIKE '%orders%'
ORDER BY mean_exec_time DESC;

sql

-- 1. Installeer pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
-- 2. Schakel slow query logging in (50ms is mijn drempel)
ALTER SYSTEM SET log_min_duration_statement = 50;
 
-- 3. SSD-geschikte instellingen
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_io_concurrency = 200;
 
-- 4. Verhoog work_mem van de karige default
ALTER SYSTEM SET work_mem = '32MB';
 
-- 5. Zet effective_cache_size op 75% van RAM
-- Voor een 16GB server:
ALTER SYSTEM SET effective_cache_size = '12GB';
 
-- 6. Zet shared_buffers op 25% van RAM
-- Voor een 16GB server:
ALTER SYSTEM SET shared_buffers = '4GB';
 
-- 7. Verbeter autovacuum-responsiviteit
ALTER SYSTEM SET autovacuum_naptime = '10s';
 
-- Pas wijzigingen toe
SELECT pg_reload_conf();
-- Let op: shared_buffers vereist een herstart

sql

-- Tabellen die aandacht nodig hebben
SELECT
  schemaname || '.' || relname AS table,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  n_live_tup,
  n_dead_tup,
  CASE WHEN n_live_tup > 0
    THEN round(100.0 * n_dead_tup / n_live_tup, 1)
    ELSE 0
  END AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

De Debug-Mentaliteit: Raad Niet, Meet

EXPLAIN — De Blauwdruk

EXPLAIN ANALYZE — De Waarheid

EXPLAIN (ANALYZE, BUFFERS) — Het Volledige Plaatje

EXPLAIN-opties Die Ik Daadwerkelijk Gebruik

Query Plans Lezen als een Detective

Seq Scan — De Volledige Tabelscan

Index Scan — De Gerichte Opzoeking

Index Only Scan — De Heilige Graal

Bitmap Scan — Het Midden

Slechte Schattingen Herkennen

Indexstrategie: De Juiste Index voor de Juiste Taak

B-tree — Het Standaard Werkpaard

Covering Indexes met INCLUDE

Partial Indexes — Index Alleen Wat Ertoe Doet

GIN Indexes — Full-Text Search en JSONB

GiST Indexes — Geometrische en Range Types

Hash Indexes — Niche maar Nuttig

Wanneer Indexen Pijn Doen

Het N+1 Query Probleem

Hoe N+1 Eruitziet

N+1 Detecteren in Logs

De Oplossing: Eager Loading of JOINs

JOINs vs Meerdere Queries

Veelvoorkomende Query-Herschrijvingen

Subquery vs JOIN vs CTE

EXISTS vs IN vs JOIN

SELECT * Vermijden

Window Functions vs Subqueries

Paginering Goed Doen

Tabelstatistieken en Vacuuming

ANALYZE: De Statistieken Bijwerken

Autovacuum Tuning

Tabel-Bloat

Connection Pooling

Waarom Je een Connection Pooler Nodig Hebt

PgBouncer-configuratie

Transaction vs Session Mode

Formule voor Pool Sizing

Praktische Checklist: De Exacte Stappen voor Elke Trage Query

Stap 1: Verkrijg de Werkelijke Query

Stap 2: Voer EXPLAIN (ANALYZE, BUFFERS) Uit

Stap 3: Controleer Tabelstatistieken

Stap 4: Controleer Bestaande Indexen

Stap 5: Maak of Wijzig Indexen

Stap 6: Overweeg Query-Herschrijvingen

Stap 7: Controleer Serverinstellingen

Stap 8: Monitor Na de Fix

Bonus: Quick Wins Die Ik op Elke Nieuwe Database Toepas

Slotgedachten

Gerelateerde artikelen

How to Compress Images Without Losing Quality: The Complete 2026 Guide

Moderne Authenticatie in 2026: JWT, Sessions, OAuth en Passkeys