explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eUhJ

Settings
# exclusive inclusive rows x rows loops node
1. 1,076.013 2,247.862 ↓ 0.0 0 1

Update on dlo_fed_reg_pat_ss_benefit pat_benefit (cost=29,823.27..47,672.98 rows=82 width=54) (actual time=2,247.862..2,247.862 rows=0 loops=1)

  • Buffers: shared hit=1669190 read=2250 dirtied=2250
2. 282.430 1,171.849 ↓ 1,547.9 126,930 1

Hash Join (cost=29,823.27..47,672.98 rows=82 width=54) (actual time=465.162..1,171.849 rows=126,930 loops=1)

  • Hash Cond: ((pat_benefit.reg2_id = reg2.id) AND (pb.benefit_def_id = reg2.definition_id))
  • Join Filter: ((pb.from_dt = reg2.date_bl) OR (pb.from_dt IS NULL))
  • Rows Removed by Join Filter: 271827
  • Buffers: shared hit=18896
3. 306.427 806.027 ↓ 8.7 501,996 1

Hash Join (cost=17,320.07..34,701.35 rows=57,404 width=56) (actual time=381.463..806.027 rows=501,996 loops=1)

  • Hash Cond: (pat_benefit.pat_ss_id = pat_ss.id)
  • Buffers: shared hit=11044
4. 118.253 118.253 ↑ 2.3 374,601 1

Seq Scan on dlo_fed_reg_pat_ss_benefit pat_benefit (cost=0.00..13,516.99 rows=877,399 width=26) (actual time=0.018..118.253 rows=374,601 loops=1)

  • Buffers: shared hit=4743
5. 80.059 381.347 ↓ 5.7 265,838 1

Hash (cost=16,736.20..16,736.20 rows=46,710 width=34) (actual time=381.347..381.347 rows=265,838 loops=1)

  • Buckets: 524288 (originally 65536) Batches: 1 (originally 1) Memory Usage: 22267kB
  • Buffers: shared hit=6301
6. 105.375 301.288 ↓ 5.7 265,838 1

Hash Join (cost=9,497.06..16,736.20 rows=46,710 width=34) (actual time=107.939..301.288 rows=265,838 loops=1)

  • Hash Cond: (pb.patient_id = pat_ss.pat_id)
  • Buffers: shared hit=6301
7. 38.281 88.591 ↓ 2.8 133,045 1

Hash Join (cost=1.05..6,597.92 rows=46,710 width=28) (actual time=0.036..88.591 rows=133,045 loops=1)

  • Hash Cond: (pb.benefit_type_id = pbt.id)
  • Buffers: shared hit=4204
8. 50.288 50.288 ↑ 1.0 140,129 1

Seq Scan on pci_benefit pb (cost=0.00..5,604.29 rows=140,129 width=26) (actual time=0.007..50.288 rows=140,129 loops=1)

  • Buffers: shared hit=4203
9. 0.001 0.022 ↑ 1.0 1 1

Hash (cost=1.04..1.04 rows=1 width=10) (actual time=0.022..0.022 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
10. 0.021 0.021 ↑ 1.0 1 1

Seq Scan on pci_benefit_type pbt (cost=0.00..1.04 rows=1 width=10) (actual time=0.020..0.021 rows=1 loops=1)

  • Filter: ((code)::text = '2'::text)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=1
11. 53.335 107.322 ↑ 1.5 219,074 1

Hash (cost=5,385.45..5,385.45 rows=328,845 width=14) (actual time=107.322..107.322 rows=219,074 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 14366kB
  • Buffers: shared hit=2097
12. 53.987 53.987 ↑ 1.5 219,230 1

Seq Scan on dlo_tmp_fed_registr_pat_ss pat_ss (cost=0.00..5,385.45 rows=328,845 width=14) (actual time=0.008..53.987 rows=219,230 loops=1)

  • Buffers: shared hit=2097
13. 32.869 83.392 ↑ 1.5 124,409 1

Hash (cost=9,712.48..9,712.48 rows=186,048 width=18) (actual time=83.392..83.392 rows=124,409 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 8366kB
  • Buffers: shared hit=7852
14. 50.523 50.523 ↑ 1.5 124,822 1

Seq Scan on tmp_reg2_gsp reg2 (cost=0.00..9,712.48 rows=186,048 width=18) (actual time=0.019..50.523 rows=124,822 loops=1)

  • Filter: is_new
  • Buffers: shared hit=7852