v(i)b(e)c(o)d(e)4u.com
Назад на форум vbcd4u.com
🔥 Нейросети2026-11-17T10:01:00.000Z▲ 0 upvotes

UITLEG ANALYSE in PostgreSQL: hoe je een plan leest en welke indexen echt nodig zijn

@murdasoftРедакция vbcd4u · MurdaSoft
Открыть все обсуждения →

Waar te beginnen: niet met een plan, maar met een lijst met verzoeken

Indexen worden toegevoegd voor specifieke zoekopdrachten, en niet “voor alle kolommen van WAAR”. Zoek eerst uit welke zoekopdrachten echt duur zijn. De extensie pg_stat_statements verzamelt statistieken over genormaliseerde zoekopdrachten:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements; -- требует shared_preload_libraries

SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time, 1) AS mean_ms,
       left(query, 120) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

Sorteren op total_exec_time laat zien waar de database in totaal zijn tijd doorbrengt. Een zoekopdracht van 3 ms die een miljoen keer wordt opgeroepen, is vaak belangrijker dan een rapport van 2 seconden eenmaal per dag.

Hoe een plan te verwijderen

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at
FROM orders
WHERE user_id = 4815 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

ANALYZE voert het verzoek daadwerkelijk uit. Voor UPDATE en DELETE verpakken in een rollback-transactie:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) DELETE FROM sessions WHERE expires_at < now();
ROLLBACK;

BUFFERS laat zien hoeveel pagina's er zijn gelezen uit de cache (shared hit) en vanaf schijf (read). In PostgreSQL 18 wordt deze informatie automatisch samen met ANALYZE weergegeven; in eerdere versies moet de parameter expliciet worden opgegeven.

Hoe knooppunten te lezen

Plan - boom. Lees van binnen naar buiten: de meest geneste knooppunten worden als eerste uitgevoerd. Elk knooppunt heeft twee groepen getallen:

Limit  (cost=0.00..1843.20 rows=20 width=24) (actual time=0.041..96.512 rows=20 loops=1)
  ->  Sort  (cost=... rows=1180 ...) (actual time=96.501..96.506 rows=20 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 27kB
        ->  Seq Scan on orders  (cost=0.00..41250.00 rows=1180 width=24)
                                (actual time=0.030..95.880 rows=1342 loops=1)
              Filter: ((user_id = 4815) AND (status = 'paid'::text))
              Rows Removed by Filter: 1998658

Dit is een illustratief plan, maar het patroon is typisch:

  • `cost` — evaluatie van de planning in conventionele eenheden. Het heeft geen zin om het tussen verschillende verzoeken te vergelijken.
  • `actual time=A..B` — milliseconden vóór de eerste regel en vóór de laatste. De tijd van de ouders omvat ook de tijd van de kinderen.
  • `rows` in de beoordeling en in de feiten. Een discrepantie in ordes van grootte is het belangrijkste teken van een probleem: de planner heeft een plan gekozen voor het verkeerde aantal lijnen.
  • `loops` — hoe vaak het knooppunt is uitgevoerd. Realtime en strings moeten worden vermenigvuldigd met loops. Intern genest lusknooppunt met loops=50000 en tijd 0,02 ms is een seconde.
  • `Rows Removed by Filter` - regels tevergeefs gelezen. Hier werden twee miljoen regels doorgenomen omwille van de 1342 benodigde regels.

Welke index heeft deze zoekopdracht nodig?

De zoekopdracht filtert op user_id en status en sorteert op created_at. De volgorde van de kolommen in een samengestelde index is eerst gelijke kolommen en vervolgens de kolom sorteren.

CREATE INDEX CONCURRENTLY orders_user_status_created_idx
  ON orders (user_id, status, created_at DESC);

Met een dergelijke index verandert het plan in Index Scan met Limit: de database leest precies 20 records per index in de vereiste volgorde en sorteert niets. CONCURRENTLY blokkeert het schrijven naar de tabel tijdens de constructie niet, maar werkt niet binnen een transactie en laat, als dit mislukt, een ongeldige index achter die moet worden verwijderd en opnieuw moet worden gemaakt.

Als bijna alle zoekopdrachten naar één enkele statuswaarde kijken, is een gedeeltelijke index kleiner en goedkoper in onderhoud:

CREATE INDEX CONCURRENTLY orders_pending_idx
  ON orders (created_at) WHERE status = 'pending';

Het wordt alleen gebruikt als de queryvoorwaarde letterlijk status = 'pending' bevat.

Knopen om naar te kijken

  • `Sort Method: external merge Disk: ...` — de sortering paste niet in work_mem en ging naar schijf. Het wordt behandeld met een index met de gewenste volgorde of oplopend work_mem voor een specifieke sessie, en niet globaal.
  • `Bitmap Heap Scan` met `Recheck Cond` en `Heap Blocks: lossy` - de bitmap paste niet in het geheugen en werd gewisseld.
  • `Index Only Scan` met een grote `Heap Fetches` - de index dekt de zoekopdracht, maar de zichtbaarheidskaart is verouderd en de database gaat nog steeds naar de tabel. VACUUM helpt (of autovacuüm instellen voor deze tabel).
  • `Hash Join` met `Batches: 8` - de hashtabel paste niet in het geheugen.
  • Schatting `rows=1`, maar feit - duizenden. Update statistieken ANALYZE orders;. Als de kolommen gecorreleerd zijn (stad en land), helpen geavanceerde statistieken: CREATE STATISTICS ... (dependencies) ON city, country FROM addresses;.

Waarom is er een index en wordt deze niet gebruikt?

  1. 1.Functie of cast op een kolom: WHERE lower(email) = $1 gebruikt geen index op email. We hebben een index nodig op basis van de expressie (lower(email)).
  2. 2.LIKE '%abc' met leidend percentage. B-tree helpt hier niet, je hebt pg_trgm met GIN nodig.
  3. 3.De voorwaarde selecteert een groot deel van de tabel. Sequentieel uitlezen is dan inderdaad goedkoper, en de planner heeft gelijk.
  4. 4.Verschillende typen: kolom bigint, parameter doorgegeven als numeric.
  5. 5.Kleine tafel. Voor een paar duizend regels is Seq Scan normaal.

Extra indexen kosten ook geld

Elke index vertraagt het invoegen en bijwerken en neemt ruimte in beslag. Kandidaten voor verwijdering:

SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Statistieken zijn lokaal op de server: de index, die niet nodig is op de primaire server, kan rapporten over de replica weergeven. En verwijder geen indexen die UNIQUE of de primaire sleutel bevatten.

Wanneer EXPLAIN het probleem niet laat zien

Als een query snel is bij een handmatige uitvoering, maar traag bij de productie, kan dit te wijten zijn aan blokkering, verbindingsconflicten of een ander plan voor de geparametriseerde query. Schakel auto_explain in met de drempelwaarde auto_explain.log_min_duration om het langzame uitvoeringsplan uit de logboeken te halen, en let op pg_stat_activity voor de verwachtingen van Lock.

Обсуждение (0)

Ещё на vbcd4u.com