Forums PostgreSQL.fr

LudovicG

Membre

analyse plan d'exécution : comprendre le "temp ... written="

Bonjour,

J'essaye de comprendre comment identifier les fichiers temporaires dans un plan d'execution (avec les options ANALYZE BUFFERS).
Autrement dit, faire le raccord avec ce que je vois dans un rapport pgBadger.

Outre les "Disk" assez explicites, je pense me pencher vers l'information "written" comme dans cet exemple :

Buffers: shared hit=364604, temp read=91606 written=97586

Et là, j'ai un doute.
Est-ce que la ligne Buffers est propre à son nœud ou représente aussi ses sous-nœuds ?

Autrement dit, si j'essaye d'optimiser la même requête SQL et que je passe de :

Buffers: shared hit=913220, temp read=102462 written=109406
Buffers: shared hit=913220, temp read=102462 written=109406
Buffers: shared hit=913220, temp read=101442 written=108383
Buffers: shared hit=913220, temp read=101442 written=108383
Buffers: shared hit=913220, temp read=101442 written=108383
Buffers: shared hit=913220, temp read=101442 written=108383
Buffers: shared hit=913190, temp read=100422 written=107360
Buffers: shared hit=734312, temp read=8877 written=9232
Buffers: shared hit=723711, temp read=8877 written=9232
Buffers: shared hit=643019, temp read=8877 written=9232
Buffers: shared hit=562327, temp read=8877 written=9232
Buffers: shared hit=496048, temp read=8877 written=9232
Buffers: shared hit=26335, temp written=8120
Buffers: shared hit=178856, temp written=91984

à ça :

Buffers: shared hit=640678, temp read=93883 written=100434
Buffers: shared hit=640678, temp read=92863 written=99411
Buffers: shared hit=640678, temp read=92863 written=99411
Buffers: shared hit=640678, temp read=92863 written=99411
Buffers: shared hit=640678, temp read=92863 written=99411
Buffers: shared hit=640648, temp read=91843 written=98388
Buffers: shared hit=178856, temp written=93000

Est-ce que j'ai réduit les écritures de :
- 109406-100434=8972 blocs
ou de :
- 905968-689466=216502 blocs

---

Ludovic

pifor

Membre

Re : analyse plan d'exécution : comprendre le "temp ... written="

La ligne Buffers cumule les statistiques Buffers du noeud courant avec celles des sous-nœuds.

D'après https://www.postgresql.org/docs/17/sql-explain.html

BUFFERS

    Include information on buffer usage. Specifically, include the number of shared blocks hit, read, dirtied, and written, the number of local blocks hit, read, dirtied, and written, the number of temp blocks read and written, and the time spent reading and writing data file blocks, local blocks and temporary file blocks (in milliseconds) if track_io_timing is enabled. A hit means that a read was avoided because the block was found already in cache when needed. Shared blocks contain data from regular tables and indexes; local blocks contain data from temporary tables and indexes; while temporary blocks contain short-term working data used in sorts, hashes, Materialize plan nodes, and similar cases. The number of blocks dirtied indicates the number of previously unmodified blocks that were changed by this query; while the number of blocks written indicates the number of previously-dirtied blocks evicted from cache by this backend during query processing. The number of blocks shown for an upper-level node includes those used by all its child nodes. In text format, only non-zero values are printed.This parameter defaults to FALSE.

Dernière modification par pifor (Aujourd'hui 18:48:22)

---

Pierre