explain.depesz.com

PostgreSQL's explain analyze made readable

Result: e2WX

Settings
# exclusive inclusive rows x rows loops node
1. 5.376 974.178 ↓ 1.8 1,516 1

Nested Loop (cost=1,078.22..1,076,900.02 rows=855 width=23) (actual time=0.457..974.178 rows=1,516 loops=1)

  • 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))))
2.          

CTE new_revise

3. 0.089 6.400 ↑ 1.0 20 1

Nested Loop (cost=54.94..1,076.79 rows=20 width=78) (actual time=0.348..6.4 rows=20 loops=1)

4. 0.056 0.111 ↑ 1.0 20 1

Hash Join (cost=1.27..2.75 rows=20 width=52) (actual time=0.033..0.111 rows=20 loops=1)

5. 0.043 0.043 ↑ 1.0 20 1

Seq Scan on revise l (cost=0..1.2 rows=20 width=48) (actual time=0.013..0.043 rows=20 loops=1)

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

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

7. 0.007 0.007 ↑ 1.0 12 1

Seq Scan on currency c (cost=0..1.12 rows=12 width=8) (actual time=0.005..0.007 rows=12 loops=1)

8. 0.060 6.200 ↓ 0.0 0 20

Limit (cost=53.67..53.67 rows=1 width=4) (actual time=0.309..0.31 rows=0 loops=20)

9. 0.220 6.140 ↓ 0.0 0 20

Sort (cost=53.67..53.67 rows=1 width=4) (actual time=0.307..0.307 rows=0 loops=20)

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

Seq Scan on revise r2 (cost=0..53.66 rows=1 width=4) (actual time=0.261..0.296 rows=0 loops=20)

  • 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))
11. 3.731 959.386 ↑ 1.1 2,354 1

Nested Loop (cost=1.14..1,074,880.94 rows=2,564 width=51) (actual time=0.424..959.386 rows=2,354 loops=1)

12. 0.828 25.825 ↑ 1.1 2,354 1

Nested Loop (cost=0.57..1,073,255.84 rows=2,564 width=47) (actual time=0.391..25.825 rows=2,354 loops=1)

13. 6.477 6.477 ↑ 1.0 20 1

CTE Scan on new_revise nr (cost=0..0.4 rows=20 width=28) (actual time=0.352..6.477 rows=20 loops=1)

14. 18.520 18.520 ↑ 10.7 118 20

Index Scan using idx_money_movement_supplier_contract_id on money_movement mm (cost=0.57..53,650.12 rows=1,265 width=35) (actual time=0.079..0.926 rows=118 loops=20)

  • Index Cond: (supplier_contract_id = nr.supplier_contract_id)
  • Filter: ((nr.currency_id = currency_id) AND ((nr.register_type_id = 3) OR (nr.register_type_id = register_type_id)) AND ((dt)::date <= nr.rev_dt))
15. 929.830 929.830 ↑ 1.0 1 2,354

Index Scan using ix_document_edition_doc_edit_id on document_edition de (cost=0.57..0.63 rows=1 width=8) (actual time=0.395..0.395 rows=1 loops=2,354)

  • Index Cond: (doc_edit_id = mm.doc_edit_id)
16. 9.416 9.416 ↑ 1.0 1 2,354

Index Scan using idx_balance_entry_supplier_contract_id_currency_id on balance_entry be (cost=0.29..0.31 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=2,354)

  • Index Cond: ((mm.supplier_contract_id = supplier_contract_id) AND (mm.currency_id = currency_id))
  • Filter: (NOT is_deleted)