explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OADcK

Settings
# exclusive inclusive rows x rows loops node
1. 690.735 7,963.049 ↑ 11.2 145,569 1

Merge Join (cost=363,884.76..78,795,214.61 rows=1,628,151 width=33) (actual time=3,528.371..7,963.049 rows=145,569 loops=1)

  • Merge Cond: (((v_ma.transaction_id)::text = (m.transaction_id)::text) AND (v_ma.output_index = m.output_index))
  • Buffers: shared hit=41495 read=333574, temp read=40495 written=40495
2.          

CTE suspended_rules

3. 0.017 0.029 ↓ 0.0 0 1

Hash Right Join (cost=72.21..114.51 rows=1,360 width=185) (actual time=0.029..0.029 rows=0 loops=1)

  • Hash Cond: (rel.uidrule = ru.uidrule)
4. 0.000 0.000 ↓ 0.0 0

Seq Scan on spunta_aux_rule_unmatched_relationship rel (cost=0.00..23.60 rows=1,360 width=32) (never executed)

5. 0.000 0.012 ↓ 0.0 0 1

Hash (cost=57.21..57.21 rows=1,200 width=185) (actual time=0.012..0.012 rows=0 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 16kB
6. 0.006 0.012 ↓ 0.0 0 1

Hash Right Join (cost=37.00..57.21 rows=1,200 width=185) (actual time=0.012..0.012 rows=0 loops=1)

  • Hash Cond: (c.uidrule = ru.uidrule)
7. 0.000 0.000 ↓ 0.0 0

Seq Scan on spunta_aux_rule_unmatched_cause c (cost=0.00..14.30 rows=430 width=161) (never executed)

8. 0.000 0.006 ↓ 0.0 0 1

Hash (cost=22.00..22.00 rows=1,200 width=40) (actual time=0.006..0.006 rows=0 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 16kB
9. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on spunta_aux_rule_unmatched ru (cost=0.00..22.00 rows=1,200 width=40) (actual time=0.006..0.006 rows=0 loops=1)

10. 3,033.337 3,033.337 ↑ 1.0 3,928,650 1

Index Only Scan using idx_vault_states_key_and_status on vault_states v_ma (cost=0.56..879,964.32 rows=4,021,744 width=69) (actual time=0.084..3,033.337 rows=3,928,650 loops=1)

  • Heap Fetches: 2805587
  • Buffers: shared hit=41493 read=261165
11. 126.836 4,238.977 ↑ 1.0 1,135,776 1

Materialize (cost=363,681.29..369,589.14 rows=1,181,570 width=114) (actual time=3,527.429..4,238.977 rows=1,135,776 loops=1)

  • Buffers: shared hit=2 read=72409, temp read=40495 written=40495
12. 3,103.646 4,112.141 ↑ 1.0 1,135,776 1

Sort (cost=363,681.29..366,635.22 rows=1,181,570 width=114) (actual time=3,527.426..4,112.141 rows=1,135,776 loops=1)

  • Sort Key: m.transaction_id, m.output_index
  • Sort Method: external merge Disk: 139920kB
  • Buffers: shared hit=2 read=72409, temp read=40495 written=40495
13. 1,008.495 1,008.495 ↑ 1.0 1,135,776 1

Seq Scan on spunta_record m (cost=0.00..99,116.52 rows=1,181,570 width=114) (actual time=6.872..1,008.495 rows=1,135,776 loops=1)

  • Filter: ((uidmatch IS NULL) AND (uidrelationship = 'b949fe59-2f18-4cad-aa58-08daa8b3e51c'::uuid))
  • Rows Removed by Filter: 1000689
  • Buffers: shared hit=2 read=72409
14.          

SubPlan (for Merge Join)

15. 0.000 0.000 ↓ 0.0 0 145,569

CTE Scan on suspended_rules ru_1 (cost=0.00..47.60 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=145,569)

  • Filter: (((cause IS NULL) OR ((cause)::text = (m.cause)::text)) AND ((uidrelationship IS NULL) OR (uidrelationship = m.uidrelationship)) AND ((maxdatedelta IS NULL) OR (('2020-01-01'::date - m.dateaccounting) <= maxdatedelta)) AND ((maxvaluedelta IS NULL) OR (abs(m.value) <= maxvaluedelta)))
Planning time : 0.871 ms
Execution time : 8,006.936 ms