explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZBly

Settings
# exclusive inclusive rows x rows loops node
1. 10,651.909 14,430.118 ↓ 0.0 0 1

Update on spunta_aux_record aux (cost=394,949.93..709,502.37 rows=228,993 width=1,097) (actual time=14,430.118..14,430.118 rows=0 loops=1)

  • Buffers: shared hit=2216302 read=565369 dirtied=394401 written=382927, temp read=7315 written=7189
2.          

CTE match

3. 10.430 1,248.765 ↑ 5.4 17,073 1

Nested Loop (cost=109,810.33..252,639.96 rows=92,155 width=31) (actual time=1,014.780..1,248.765 rows=17,073 loops=1)

  • Buffers: shared hit=82685 read=84987 written=1868
4. 97.413 1,084.678 ↑ 3.3 17,073 1

Hash Join (cost=109,809.78..125,952.68 rows=55,995 width=100) (actual time=1,013.881..1,084.678 rows=17,073 loops=1)

  • Hash Cond: (match.uid = m_1.uidmatch)
  • Buffers: shared hit=4 read=81475
5. 97.019 97.019 ↑ 1.0 497,554 1

Seq Scan on spunta_match match (cost=0.00..14,043.76 rows=497,576 width=100) (actual time=0.018..97.019 rows=497,554 loops=1)

  • Buffers: shared hit=2 read=9066
6. 2.919 890.246 ↑ 3.1 17,073 1

Hash (cost=109,153.65..109,153.65 rows=52,490 width=16) (actual time=890.246..890.246 rows=17,073 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 1313kB
  • Buffers: shared hit=2 read=72409
7. 4.973 887.327 ↑ 3.1 17,074 1

Unique (cost=108,873.68..109,153.65 rows=52,490 width=16) (actual time=877.679..887.327 rows=17,074 loops=1)

  • Buffers: shared hit=2 read=72409
8. 21.057 882.354 ↑ 1.0 55,186 1

Sort (cost=108,873.68..109,013.67 rows=55,995 width=16) (actual time=877.678..882.354 rows=55,186 loops=1)

  • Sort Key: m_1.uidmatch
  • Sort Method: quicksort Memory: 3692kB
  • Buffers: shared hit=2 read=72409
9. 861.297 861.297 ↑ 1.0 55,186 1

Seq Scan on spunta_record m_1 (cost=0.00..104,457.63 rows=55,995 width=16) (actual time=3.288..861.297 rows=55,186 loops=1)

  • Filter: ((uidreportupload = ANY ('{113f4adf-4fed-4b24-be6c-19be20a21c46,ca7c28ed-bbd0-4dda-8ac4-d5b836985f6d}'::uuid[])) AND (uidrelationship = 'b949fe59-2f18-4cad-aa58-08daa8b3e51c'::uuid))
  • Rows Removed by Filter: 2081279
  • Buffers: shared hit=2 read=72409
10. 153.657 153.657 ↑ 2.0 1 17,073

Index Only Scan using idx_vault_states_key_and_status on vault_states v_ma (cost=0.56..2.24 rows=2 width=69) (actual time=0.008..0.009 rows=1 loops=17,073)

  • Index Cond: ((transaction_id = (match.transaction_id)::text) AND (output_index = match.output_index))
  • Heap Fetches: 17073
  • Buffers: shared hit=82681 read=3512 written=1868
11. 23.644 3,778.209 ↑ 6.7 34,146 1

Nested Loop (cost=142,309.96..456,862.40 rows=228,993 width=1,097) (actual time=2,362.014..3,778.209 rows=34,146 loops=1)

  • Buffers: shared hit=220417 read=191673 written=33915, temp read=7315 written=7189
12. 228.984 2,832.623 ↑ 7.1 34,146 1

Hash Join (cost=142,309.54..203,635.71 rows=242,249 width=142) (actual time=2,361.977..2,832.623 rows=34,146 loops=1)

  • Hash Cond: (ma.uidmatch = m.uidmatch)
  • Buffers: shared hit=82719 read=157364 written=1868, temp read=7315 written=7189
13. 1,261.047 1,261.047 ↑ 5.4 17,073 1

CTE Scan on match ma (cost=0.00..1,843.10 rows=92,155 width=120) (actual time=1,014.794..1,261.047 rows=17,073 loops=1)

  • Buffers: shared hit=82685 read=84987 written=1868
14. 357.159 1,342.592 ↑ 2.1 990,207 1

Hash (cost=99,116.52..99,116.52 rows=2,126,401 width=38) (actual time=1,342.592..1,342.592 rows=990,207 loops=1)

  • Buckets: 65536 Batches: 64 Memory Usage: 1563kB
  • Buffers: shared hit=34 read=72377, temp written=6869
15. 985.433 985.433 ↑ 1.0 2,125,983 1

Seq Scan on spunta_record m (cost=0.00..99,116.52 rows=2,126,401 width=38) (actual time=0.021..985.433 rows=2,125,983 loops=1)

  • Filter: (uidrelationship = 'b949fe59-2f18-4cad-aa58-08daa8b3e51c'::uuid)
  • Rows Removed by Filter: 10482
  • Buffers: shared hit=34 read=72377
16. 921.942 921.942 ↑ 1.0 1 34,146

Index Scan using spunta_aux_record_pkey on spunta_aux_record aux (cost=0.42..1.04 rows=1 width=971) (actual time=0.025..0.027 rows=1 loops=34,146)

  • Index Cond: (uid = m.uid)
  • Buffers: shared hit=137698 read=34309 written=32047
Planning time : 8.962 ms
Execution time : 14,430.500 ms