explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xOJB

Settings
# exclusive inclusive rows x rows loops node
1. 956.454 2,004.366 ↓ 0.0 0 1

Update on dlo_fed_reg_pat_ss_benefit pat_benefit (cost=22,543.29..30,783.18 rows=128 width=54) (actual time=2,004.366..2,004.366 rows=0 loops=1)

  • Buffers: shared hit=1581742 read=2592 dirtied=2592
2. 248.100 1,047.912 ↓ 991.6 126,930 1

Hash Join (cost=22,543.29..30,783.18 rows=128 width=54) (actual time=434.436..1,047.912 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=12895
3. 280.779 721.471 ↓ 8.7 501,996 1

Hash Join (cost=14,154.74..21,904.54 rows=57,404 width=56) (actual time=355.909..721.471 rows=501,996 loops=1)

  • Hash Cond: (pat_benefit.pat_ss_id = pat_ss.id)
  • Buffers: shared hit=7627
4. 84.902 84.902 ↑ 1.0 374,601 1

Seq Scan on dlo_fed_reg_pat_ss_benefit pat_benefit (cost=0.00..5,771.01 rows=374,601 width=26) (actual time=0.008..84.902 rows=374,601 loops=1)

  • Buffers: shared hit=2025
5. 72.690 355.790 ↓ 5.7 265,838 1

Hash (cost=13,570.86..13,570.86 rows=46,710 width=34) (actual time=355.790..355.790 rows=265,838 loops=1)

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

Hash Join (cost=6,331.73..13,570.86 rows=46,710 width=34) (actual time=93.446..283.100 rows=265,838 loops=1)

  • Hash Cond: (pb.patient_id = pat_ss.pat_id)
  • Buffers: shared hit=5602
7. 30.701 79.340 ↓ 2.8 133,045 1

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

  • Hash Cond: (pb.benefit_type_id = pbt.id)
  • Buffers: shared hit=4204
8. 48.626 48.626 ↑ 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.006..48.626 rows=140,129 loops=1)

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

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

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

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

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

Hash (cost=3,590.30..3,590.30 rows=219,230 width=14) (actual time=93.106..93.106 rows=219,074 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 12318kB
  • Buffers: shared hit=1398
12. 49.710 49.710 ↑ 1.0 219,230 1

Seq Scan on dlo_tmp_fed_registr_pat_ss pat_ss (cost=0.00..3,590.30 rows=219,230 width=14) (actual time=0.010..49.710 rows=219,230 loops=1)

  • Buffers: shared hit=1398
13. 26.462 78.341 ↑ 1.0 124,409 1

Hash (cost=6,516.22..6,516.22 rows=124,822 width=18) (actual time=78.341..78.341 rows=124,409 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 7342kB
  • Buffers: shared hit=5268
14. 51.879 51.879 ↑ 1.0 124,822 1

Seq Scan on tmp_reg2_gsp reg2 (cost=0.00..6,516.22 rows=124,822 width=18) (actual time=0.009..51.879 rows=124,822 loops=1)

  • Filter: is_new
  • Buffers: shared hit=5268