explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 39al

Settings
# exclusive inclusive rows x rows loops node
1. 0.816 589,523.201 ↑ 1.0 1 1

Aggregate (cost=22,884,301.47..22,884,301.48 rows=1 width=8) (actual time=589,523.200..589,523.201 rows=1 loops=1)

2.          

CTE new_revise

3. 0.046 2.811 ↑ 96.4 11 1

Nested Loop Left Join (cost=54.94..56,961.10 rows=1,060 width=78) (actual time=0.349..2.811 rows=11 loops=1)

4. 0.036 0.092 ↑ 96.4 11 1

Hash Left Join (cost=1.27..36.45 rows=1,060 width=53) (actual time=0.060..0.092 rows=11 loops=1)

  • Hash Cond: (l.currency_id = c.currency_code)
5. 0.029 0.029 ↑ 96.4 11 1

Seq Scan on revise l (cost=0.00..20.60 rows=1,060 width=49) (actual time=0.019..0.029 rows=11 loops=1)

  • Filter: (NOT is_deleted)
6. 0.012 0.027 ↑ 1.0 12 1

Hash (cost=1.12..1.12 rows=12 width=8) (actual time=0.026..0.027 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
7. 0.015 0.015 ↑ 1.0 12 1

Seq Scan on currency c (cost=0.00..1.12 rows=12 width=8) (actual time=0.008..0.015 rows=12 loops=1)

8. 0.033 2.673 ↓ 0.0 0 11

Limit (cost=53.67..53.67 rows=1 width=4) (actual time=0.242..0.243 rows=0 loops=11)

9. 0.066 2.640 ↓ 0.0 0 11

Sort (cost=53.67..53.67 rows=1 width=4) (actual time=0.240..0.240 rows=0 loops=11)

  • Sort Key: r2.rev_dt DESC
  • Sort Method: quicksort Memory: 25kB
10. 2.574 2.574 ↓ 0.0 0 11

Seq Scan on revise r2 (cost=0.00..53.66 rows=1 width=4) (actual time=0.199..0.234 rows=0 loops=11)

  • Filter: ((NOT is_deleted) AND (rev_dt < l.rev_dt) AND (l.suppliercontract_id = supplier_contract_id) AND (COALESCE(c.currency_id, l.currency_id) = currency_id))
  • Rows Removed by Filter: 1810
11. 4.934 589,522.385 ↓ 1.4 730 1

Nested Loop (cost=6,135,013.94..22,827,339.09 rows=515 width=0) (actual time=81,773.624..589,522.385 rows=730 loops=1)

  • Join Filter: (((mm.dt)::date > GREATEST(be.doc_dt, nr.prior_rev_dt, '1900-01-01'::date)) OR ((de.doc_type_id = 240) AND (nr.register_type_id = 1) AND ((mm.dt)::date > GREATEST(nr.prior_rev_dt, '1900-01-01'::date))) OR ((de.doc_type_id = 241) AND (nr.register_type_id = 2) AND ((mm.dt)::date > GREATEST(nr.prior_rev_dt, '1900-01-01'::date))) OR ((de.doc_type_id = 242) AND (nr.register_type_id = 3) AND ((mm.dt)::date > GREATEST(nr.prior_rev_dt, '1900-01-01'::date))))
  • Rows Removed by Join Filter: 384
12. 130,836.193 589,499.627 ↑ 1.4 1,114 1

Merge Join (cost=6,135,013.37..22,825,447.62 rows=1,546 width=24) (actual time=81,773.571..589,499.627 rows=1,114 loops=1)

  • Merge Cond: ((mm.currency_id = nr.currency_id) AND (mm.supplier_contract_id = nr.supplier_contract_id))
  • Join Filter: (((nr.register_type_id = 3) OR (nr.register_type_id = mm.register_type_id)) AND ((mm.dt)::date <= nr.rev_dt))
  • Rows Removed by Join Filter: 1083
13. 322,942.332 458,658.346 ↑ 1.0 127,189,192 1

Gather Merge (cost=6,134,938.91..22,162,615.81 rows=129,994,557 width=28) (actual time=72,164.041..458,658.346 rows=127,189,192 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
14. 40,449.290 135,716.014 ↑ 1.3 25,438,612 5 / 5

Merge Left Join (cost=6,133,938.85..6,678,010.81 rows=32,498,639 width=28) (actual time=72,105.471..135,716.014 rows=25,438,612 loops=5)

  • Merge Cond: ((mm.currency_id = be.currency_id) AND (mm.supplier_contract_id = be.supplier_contract_id))
15. 61,629.893 95,070.501 ↑ 1.3 25,438,612 5 / 5

Sort (cost=6,127,317.53..6,208,564.12 rows=32,498,639 width=24) (actual time=71,991.446..95,070.501 rows=25,438,612 loops=5)

  • Sort Key: mm.currency_id, mm.supplier_contract_id
  • Sort Method: external merge Disk: 873600kB
  • Worker 0: Sort Method: external merge Disk: 872576kB
  • Worker 1: Sort Method: external merge Disk: 843432kB
  • Worker 2: Sort Method: external merge Disk: 865296kB
  • Worker 3: Sort Method: external merge Disk: 870248kB
16. 33,440.608 33,440.608 ↑ 1.3 25,996,102 5 / 5

Parallel Seq Scan on money_movement mm (cost=0.00..1,682,117.39 rows=32,498,639 width=24) (actual time=0.077..33,440.608 rows=25,996,102 loops=5)

17. 151.712 196.223 ↑ 1.1 61,697 5 / 5

Sort (cost=6,621.32..6,785.63 rows=65,722 width=12) (actual time=114.009..196.223 rows=61,697 loops=5)

  • Sort Key: be.currency_id, be.supplier_contract_id
  • Sort Method: quicksort Memory: 6153kB
  • Worker 0: Sort Method: quicksort Memory: 6153kB
  • Worker 1: Sort Method: quicksort Memory: 6153kB
  • Worker 2: Sort Method: quicksort Memory: 6153kB
  • Worker 3: Sort Method: quicksort Memory: 6153kB
18. 44.511 44.511 ↑ 1.0 65,722 5 / 5

Seq Scan on balance_entry be (cost=0.00..1,362.22 rows=65,722 width=12) (actual time=0.038..44.511 rows=65,722 loops=5)

  • Filter: (NOT is_deleted)
19. 2.239 5.088 ↓ 1.9 2,056 1

Sort (cost=74.46..77.11 rows=1,060 width=20) (actual time=2.877..5.088 rows=2,056 loops=1)

  • Sort Key: nr.currency_id, nr.supplier_contract_id
  • Sort Method: quicksort Memory: 25kB
20. 2.849 2.849 ↑ 96.4 11 1

CTE Scan on new_revise nr (cost=0.00..21.20 rows=1,060 width=20) (actual time=0.357..2.849 rows=11 loops=1)

21. 17.824 17.824 ↑ 1.0 1 1,114

Index Scan using ix_document_edition_doc_edit_id on document_edition de (cost=0.57..1.17 rows=1 width=8) (actual time=0.015..0.016 rows=1 loops=1,114)

  • Index Cond: (doc_edit_id = mm.doc_edit_id)