explain.depesz.com

PostgreSQL's explain analyze made readable

Result: G5HQ : UPDATE TO_CHECK

Settings
# exclusive inclusive rows x rows loops node
1. 116,666.420 129,151.182 ↓ 0.0 0 1

Update on usage (cost=1,019,638.42..2,930,140.42 rows=3,585,195 width=1,314) (actual time=129,151.182..129,151.182 rows=0 loops=1)

  • Buffers: shared hit=43439005 read=73763 dirtied=248073, temp read=11339 written=11339
2. 1,195.234 12,484.762 ↑ 3.5 1,014,518 1

Merge Semi Join (cost=1,019,638.42..2,930,140.42 rows=3,585,195 width=1,314) (actual time=6,081.543..12,484.762 rows=1,014,518 loops=1)

  • Merge Cond: (usage.id = usage_1.id)
  • Buffers: shared hit=1145929 dirtied=1, temp read=11339 written=11339
3. 4,762.050 4,762.050 ↑ 1.0 7,448,074 1

Index Scan using usage_pkey on usage (cost=0.43..1,829,137.91 rows=7,449,442 width=1,273) (actual time=0.007..4,762.050 rows=7,448,074 loops=1)

  • Buffers: shared hit=855143
4. 133.729 6,527.478 ↑ 3.5 1,014,518 1

Materialize (cost=1,019,637.99..1,037,563.96 rows=3,585,195 width=48) (actual time=6,081.506..6,527.478 rows=1,014,518 loops=1)

  • Buffers: shared hit=290786 dirtied=1, temp read=11339 written=11339
5. 996.142 6,393.749 ↑ 3.5 1,014,518 1

Sort (cost=1,019,637.99..1,028,600.97 rows=3,585,195 width=48) (actual time=6,081.503..6,393.749 rows=1,014,518 loops=1)

  • Sort Key: usage_1.id
  • Sort Method: external merge Disk: 57464kB
  • Buffers: shared hit=290786 dirtied=1, temp read=11339 written=11339
6. 947.199 5,397.607 ↑ 3.5 1,014,518 1

Hash Join (cost=21,582.09..408,747.62 rows=3,585,195 width=48) (actual time=740.258..5,397.607 rows=1,014,518 loops=1)

  • Hash Cond: (usage_1.segment_id = ("ANY_subquery".segment_id)::text)
  • Buffers: shared hit=290782 dirtied=1
7. 3,957.799 3,957.799 ↓ 1.0 7,467,453 1

Seq Scan on usage usage_1 (cost=0.00..357,321.42 rows=7,192,207 width=24) (actual time=0.038..3,957.799 rows=7,467,453 loops=1)

  • Filter: ((reversed_by_usage_id IS NULL) AND (original_usage_id IS NULL))
  • Rows Removed by Filter: 507808
  • Buffers: shared hit=282827
8. 10.633 492.609 ↓ 138.1 27,624 1

Hash (cost=21,579.59..21,579.59 rows=200 width=44) (actual time=492.609..492.609 rows=27,624 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2279kB
  • Buffers: shared hit=7955 dirtied=1
9. 25.598 481.976 ↓ 138.1 27,624 1

HashAggregate (cost=21,577.59..21,579.59 rows=200 width=44) (actual time=475.628..481.976 rows=27,624 loops=1)

  • Group Key: ("ANY_subquery".segment_id)::text
  • Buffers: shared hit=7955 dirtied=1
10. 6.266 456.378 ↑ 2.4 27,624 1

Subquery Scan on ANY_subquery (cost=20,404.84..21,410.05 rows=67,014 width=44) (actual time=441.611..456.378 rows=27,624 loops=1)

  • Buffers: shared hit=7955 dirtied=1
11. 5.447 450.112 ↑ 2.4 27,624 1

Group (cost=20,404.84..20,739.91 rows=67,014 width=10) (actual time=441.599..450.112 rows=27,624 loops=1)

  • Group Key: catalog.segment_id
  • Buffers: shared hit=7955 dirtied=1
12. 142.355 444.665 ↑ 2.4 27,624 1

Sort (cost=20,404.84..20,572.38 rows=67,014 width=10) (actual time=441.597..444.665 rows=27,624 loops=1)

  • Sort Key: catalog.segment_id
  • Sort Method: quicksort Memory: 2063kB
  • Buffers: shared hit=7955 dirtied=1
13. 40.871 302.310 ↑ 2.4 27,624 1

Hash Semi Join (cost=95.53..15,032.94 rows=67,014 width=10) (actual time=1.424..302.310 rows=27,624 loops=1)

  • Hash Cond: (catalog.lr_acct_id = data_term.lr_acct_id)
  • Buffers: shared hit=7952 dirtied=1
14. 260.872 260.872 ↑ 1.0 499,070 1

Seq Scan on catalog (cost=0.00..12,881.80 rows=499,080 width=14) (actual time=0.106..260.872 rows=499,070 loops=1)

  • Buffers: shared hit=7891 dirtied=1
15. 0.003 0.567 ↑ 5.4 7 1

Hash (cost=95.05..95.05 rows=38 width=4) (actual time=0.567..0.567 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=61
16. 0.005 0.564 ↑ 5.4 7 1

Group (cost=94.48..94.67 rows=38 width=4) (actual time=0.557..0.564 rows=7 loops=1)

  • Group Key: data_term.lr_acct_id
  • Buffers: shared hit=61
17. 0.025 0.559 ↓ 1.1 40 1

Sort (cost=94.48..94.58 rows=38 width=4) (actual time=0.557..0.559 rows=40 loops=1)

  • Sort Key: data_term.lr_acct_id
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=61
18. 0.163 0.534 ↓ 1.1 40 1

Hash Join (cost=44.30..93.49 rows=38 width=4) (actual time=0.292..0.534 rows=40 loops=1)

  • Hash Cond: (data_term.id = data_term_1.overrider_data_term_id)
  • Buffers: shared hit=58
19. 0.112 0.112 ↑ 1.0 1,425 1

Seq Scan on data_term (cost=0.00..43.67 rows=1,467 width=8) (actual time=0.011..0.112 rows=1,425 loops=1)

  • Buffers: shared hit=29
20. 0.005 0.259 ↓ 1.7 40 1

Hash (cost=44.01..44.01 rows=24 width=4) (actual time=0.259..0.259 rows=40 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=29
21. 0.017 0.254 ↓ 1.7 40 1

HashAggregate (cost=43.77..44.01 rows=24 width=4) (actual time=0.248..0.254 rows=40 loops=1)

  • Group Key: data_term_1.overrider_data_term_id
  • Buffers: shared hit=29
22. 0.237 0.237 ↓ 1.1 40 1

Seq Scan on data_term data_term_1 (cost=0.00..43.67 rows=38 width=4) (actual time=0.011..0.237 rows=40 loops=1)

  • Filter: (overrider_data_term_id IS NOT NULL)
  • Rows Removed by Filter: 1385
  • Buffers: shared hit=29
Planning time : 3.432 ms
Execution time : 129,161.896 ms