explain.depesz.com

PostgreSQL's explain analyze made readable

Result: knZG

Settings
# exclusive inclusive rows x rows loops node
1. 1.993 33.201 ↓ 1.8 1,516 1

Nested Loop Left Join (cost=1,078.22..1,076,901.04 rows=855 width=23) (actual time=0.406..33.201 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))))
  • Rows Removed by Filter: 838
2.          

CTE new_revise

3. 0.052 4.643 ↑ 1.0 20 1

Nested Loop Left Join (cost=54.94..1,076.79 rows=20 width=78) (actual time=0.315..4.643 rows=20 loops=1)

4. 0.031 0.071 ↑ 1.0 20 1

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

  • Hash Cond: (l.currency_id = c.currency_code)
5. 0.028 0.028 ↑ 1.0 20 1

Seq Scan on revise l (cost=0.00..1.20 rows=20 width=48) (actual time=0.013..0.028 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.011..0.012 rows=12 loops=1)

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

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

8. 0.020 4.520 ↓ 0.0 0 20

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

9. 0.120 4.500 ↓ 0.0 0 20

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

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

Seq Scan on revise r2 (cost=0.00..53.66 rows=1 width=4) (actual time=0.187..0.219 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))
  • Rows Removed by Filter: 1810
11. 0.957 26.500 ↑ 1.1 2,354 1

Nested Loop (cost=1.14..1,074,881.96 rows=2,564 width=51) (actual time=0.387..26.500 rows=2,354 loops=1)

12. 0.532 11.419 ↑ 1.1 2,354 1

Nested Loop (cost=0.57..1,073,256.86 rows=2,564 width=47) (actual time=0.368..11.419 rows=2,354 loops=1)

13. 4.687 4.687 ↑ 1.0 20 1

CTE Scan on new_revise nr (cost=0.00..0.40 rows=20 width=28) (actual time=0.319..4.687 rows=20 loops=1)

14. 6.200 6.200 ↑ 10.7 118 20

Index Scan using idx_money_movement_supplier_contract_id on money_movement mm (cost=0.57..53,650.17 rows=1,265 width=35) (actual time=0.019..0.310 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))
  • Rows Removed by Filter: 80
15. 14.124 14.124 ↑ 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.006..0.006 rows=1 loops=2,354)

  • Index Cond: (doc_edit_id = mm.doc_edit_id)
16. 4.708 4.708 ↑ 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.002..0.002 rows=1 loops=2,354)

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