explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lNQU

Settings
# exclusive inclusive rows x rows loops node
1. 0.023 40,970.899 ↑ 1.0 100 1

Limit (cost=38,847,615.77..38,847,616.02 rows=100 width=133) (actual time=40,970.870..40,970.899 rows=100 loops=1)

  • Buffers: shared hit=11927947, temp read=2358 written=783
2. 0.888 40,970.876 ↑ 72.4 100 1

Sort (cost=38,847,615.77..38,847,633.86 rows=7,238 width=133) (actual time=40,970.869..40,970.876 rows=100 loops=1)

  • Sort Key: ec.id_ecriture DESC
  • Sort Method: top-N heapsort Memory: 223kB
  • Buffers: shared hit=11927947, temp read=2358 written=783
3. 14.884 40,969.988 ↑ 1.8 3,979 1

WindowAgg (cost=7,796.53..38,847,339.14 rows=7,238 width=133) (actual time=40,965.601..40,969.988 rows=3,979 loops=1)

  • Buffers: shared hit=11927947, temp read=2358 written=783
4. 11.069 40,955.104 ↑ 1.8 3,979 1

HashAggregate (cost=7,796.53..38,847,158.19 rows=7,238 width=117) (actual time=54.235..40,955.104 rows=3,979 loops=1)

  • Group Key: ec.id_ecriture
  • Buffers: shared hit=11927947
5. 2.130 39.915 ↑ 1.2 16,488 1

Hash Join (cost=2,329.39..7,747.25 rows=19,709 width=53) (actual time=16.236..39.915 rows=16,488 loops=1)

  • Hash Cond: (ec.jaltps_id = j.jaltps_id)
  • Buffers: shared hit=3981
6. 11.950 37.295 ↑ 1.2 16,488 1

Hash Join (cost=2,298.50..7,664.40 rows=19,709 width=53) (actual time=15.739..37.295 rows=16,488 loops=1)

  • Hash Cond: (le.id_ecriture = ec.id_ecriture)
  • Buffers: shared hit=3972
7. 16.171 16.171 ↑ 1.0 189,059 1

Seq Scan on ligne_ecriture le (cost=0.00..4,869.59 rows=189,059 width=8) (actual time=0.007..16.171 rows=189,059 loops=1)

  • Buffers: shared hit=2979
8. 0.558 9.174 ↑ 1.8 3,987 1

Hash (cost=2,208.03..2,208.03 rows=7,238 width=53) (actual time=9.174..9.174 rows=3,987 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 407kB
  • Buffers: shared hit=993
9. 8.616 8.616 ↑ 1.8 3,987 1

Seq Scan on ecriture ec (cost=0.00..2,208.03 rows=7,238 width=53) (actual time=1.604..8.616 rows=3,987 loops=1)

  • Filter: ((date_piece >= '2018-01-01'::date) AND (date_piece <= '2018-12-31'::date) AND (id_etablissement = 1588))
  • Rows Removed by Filter: 65443
  • Buffers: shared hit=993
10. 0.242 0.490 ↑ 1.0 973 1

Hash (cost=18.73..18.73 rows=973 width=8) (actual time=0.490..0.490 rows=973 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
  • Buffers: shared hit=9
11. 0.248 0.248 ↑ 1.0 973 1

Seq Scan on jaltps j (cost=0.00..18.73 rows=973 width=8) (actual time=0.013..0.248 rows=973 loops=1)

  • Buffers: shared hit=9
12.          

SubPlan (forHashAggregate)

13. 3.979 40,892.183 ↑ 1.0 1 3,979

Subquery Scan on req2 (cost=5,361.36..5,361.39 rows=1 width=32) (actual time=10.277..10.277 rows=1 loops=3,979)

  • Buffers: shared hit=11919987
14. 89.229 40,888.204 ↑ 1.0 1 3,979

Aggregate (cost=5,361.36..5,361.37 rows=1 width=32) (actual time=10.276..10.276 rows=1 loops=3,979)

  • Buffers: shared hit=11919987
15. 40,733.023 40,733.023 ↑ 1.0 4 3,979

Seq Scan on ligne_ecriture lec (cost=0.00..5,342.24 rows=4 width=78) (actual time=6.220..10.237 rows=4 loops=3,979)

  • Filter: (id_ecriture = ec.id_ecriture)
  • Rows Removed by Filter: 189055
  • Buffers: shared hit=11853441
16.          

SubPlan (forAggregate)

17. 16.488 65.952 ↑ 1.0 1 16,488

Subquery Scan on req6 (cost=4.75..4.78 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=16,488)

  • Buffers: shared hit=66546
18. 16.488 49.464 ↑ 1.0 1 16,488

Aggregate (cost=4.75..4.77 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=16,488)

  • Buffers: shared hit=66546
19. 2.796 32.976 ↓ 0.0 0 16,488

Nested Loop (cost=0.71..4.75 rows=1 width=40) (actual time=0.002..0.002 rows=0 loops=16,488)

  • Buffers: shared hit=66546
20. 16.488 16.488 ↓ 0.0 0 16,488

Index Scan using ndx_sch1_sch_l_doc_ligne_ecriture on l_doc_ligne_ecriture ldle (cost=0.29..2.31 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=16,488)

  • Index Cond: (id_ligne_ecriture = lec.id_ligne_ecriture)
  • Buffers: shared hit=39153
21. 13.692 13.692 ↑ 1.0 1 6,846

Index Scan using ndx_sch1_sch_document on document de (cost=0.42..2.44 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=6,846)

  • Index Cond: (id_document = ldle.id_document)
  • Buffers: shared hit=27393
22. 3.979 11.937 ↑ 1.0 1 3,979

Subquery Scan on req4 (cost=4.62..4.64 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=3,979)

  • Buffers: shared hit=3979
23. 3.979 7.958 ↑ 1.0 1 3,979

Aggregate (cost=4.62..4.63 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=3,979)

  • Buffers: shared hit=3979
24. 0.000 3.979 ↓ 0.0 0 3,979

Nested Loop (cost=0.57..4.61 rows=1 width=40) (actual time=0.001..0.001 rows=0 loops=3,979)

  • Buffers: shared hit=3979
25. 3.979 3.979 ↓ 0.0 0 3,979

Index Scan using ndx_sch1_sch_l_doc_ecriture_2 on l_doc_ecriture lde (cost=0.15..2.17 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=3,979)

  • Index Cond: (id_ecriture = ec.id_ecriture)
  • Buffers: shared hit=3979
26. 0.000 0.000 ↓ 0.0 0

Index Scan using ndx_sch1_sch_document on document de_1 (cost=0.42..2.44 rows=1 width=24) (never executed)

  • Index Cond: (id_document = lde.id_document)