explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aohP : Optimization for: plan #EE8W

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.014 8,843.187 ↑ 4.0 1 1

Sort (cost=2,366,220.77..2,366,220.78 rows=4 width=285) (actual time=8,843.187..8,843.187 rows=1 loops=1)

  • Sort Key: mm.dt
  • Sort Method: quicksort Memory: 25kB
2. 0.017 8,843.173 ↑ 4.0 1 1

HashAggregate (cost=2,366,220.69..2,366,220.73 rows=4 width=285) (actual time=8,843.172..8,843.173 rows=1 loops=1)

  • Group Key: de2.doc_id, de2.doc_type_id, dt.doc_type_name, mm.amount, mm.currency_id, mm.rate, mm.direction, mm.dt, mm.supplier_contract_id
3. 0.003 8,843.156 ↑ 4.0 1 1

Append (cost=680,006.81..2,366,220.60 rows=4 width=285) (actual time=7,864.452..8,843.156 rows=1 loops=1)

4. 0.001 2,846.389 ↓ 0.0 0 1

Nested Loop Left Join (cost=680,006.81..1,683,573.63 rows=2 width=257) (actual time=2,846.389..2,846.389 rows=0 loops=1)

  • Join Filter: (dt.doc_type_id = de2.doc_type_id)
5. 0.001 2,846.388 ↓ 0.0 0 1

Nested Loop (cost=680,006.81..1,683,556.43 rows=1 width=39) (actual time=2,846.388..2,846.388 rows=0 loops=1)

  • Join Filter: (de2.doc_edit_id = de.ref_doc_edit_id)
6. 0.012 2,846.387 ↓ 0.0 0 1

Hash Join (cost=680,006.25..1,683,555.82 rows=1 width=47) (actual time=2,846.387..2,846.387 rows=0 loops=1)

  • Hash Cond: (mm.doc_edit_id = de2.doc_edit_id)
7. 0.024 0.024 ↑ 43,712,361.0 1 1

Seq Scan on money_movement mm (cost=0.00..888,804.61 rows=43,712,361 width=31) (actual time=0.024..0.024 rows=1 loops=1)

8. 0.000 2,846.351 ↓ 0.0 0 1

Hash (cost=680,006.24..680,006.24 rows=1 width=16) (actual time=2,846.351..2,846.351 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
9. 24.659 2,846.463 ↓ 0.0 0 1

Gather (cost=1,000.00..680,006.24 rows=1 width=16) (actual time=2,846.351..2,846.463 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
10. 2,821.804 2,821.804 ↓ 0.0 0 3

Parallel Seq Scan on document_edition de2 (cost=0.00..679,006.14 rows=1 width=16) (actual time=2,821.804..2,821.804 rows=0 loops=3)

  • Filter: ((storno_status_id = ANY ('{2,4}'::integer[])) AND (doc_id = 21751))
  • Rows Removed by Filter: 14352475
11. 0.000 0.000 ↓ 0.0 0

Index Only Scan using ix_document_edition_ref_doc_edit_id on document_edition de (cost=0.56..0.60 rows=1 width=8) (never executed)

  • Index Cond: (ref_doc_edit_id = mm.doc_edit_id)
  • Heap Fetches: 0
12. 0.000 0.000 ↓ 0.0 0

Seq Scan on document_type dt (cost=0.00..13.20 rows=320 width=222) (never executed)

13. 0.005 5,996.764 ↑ 2.0 1 1

Nested Loop Left Join (cost=1,003.36..682,646.94 rows=2 width=257) (actual time=5,018.061..5,996.764 rows=1 loops=1)

  • Join Filter: (dt_1.doc_type_id = cde.doc_type_id)
  • Rows Removed by Join Filter: 22
14. 0.000 5,996.747 ↑ 1.0 1 1

Nested Loop (cost=1,003.36..682,629.74 rows=1 width=39) (actual time=5,018.046..5,996.747 rows=1 loops=1)

  • Join Filter: (cde.doc_edit_id = de_1.doc_edit_id)
15. 219.729 6,213.553 ↑ 1.0 1 1

Gather (cost=1,002.79..682,629.13 rows=1 width=51) (actual time=5,018.011..6,213.553 rows=1 loops=1)

  • Workers Planned: 2
  • Workers Launched: 1
16. 3,410.102 5,993.824 ↓ 0.0 0 2

Hash Join (cost=2.80..681,629.03 rows=1 width=51) (actual time=5,504.541..5,993.824 rows=0 loops=2)

  • Hash Cond: (mm_1.doc_edit_id = cde.doc_edit_id)
17. 2,583.651 2,583.651 ↓ 1.2 21,685,930 2

Parallel Seq Scan on money_movement mm_1 (cost=0.00..633,815.84 rows=18,213,484 width=31) (actual time=0.016..2,583.651 rows=21,685,930 loops=2)

18. 0.004 0.071 ↑ 1.0 1 2

Hash (cost=2.78..2.78 rows=1 width=20) (actual time=0.071..0.071 rows=1 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.067 0.067 ↑ 1.0 1 2

Index Scan using uq_current_document_edition_doc_id_doc_type_id on current_document_edition cde (cost=0.56..2.78 rows=1 width=20) (actual time=0.066..0.067 rows=1 loops=2)

  • Index Cond: (doc_id = 21751)
20. 0.028 0.028 ↑ 1.0 1 1

Index Only Scan using ix_document_edition_doc_edit_id on document_edition de_1 (cost=0.56..0.60 rows=1 width=4) (actual time=0.028..0.028 rows=1 loops=1)

  • Index Cond: (doc_edit_id = mm_1.doc_edit_id)
  • Heap Fetches: 1
21. 0.012 0.012 ↑ 13.9 23 1

Seq Scan on document_type dt_1 (cost=0.00..13.20 rows=320 width=222) (actual time=0.010..0.012 rows=23 loops=1)