explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jbI

Settings
# exclusive inclusive rows x rows loops node
1. 8,445.468 495,838.979 ↓ 42.0 59,394 1

Nested Loop (cost=3,275.94..6,418.61 rows=1,414 width=512) (actual time=2,194.767..495,838.979 rows=59,394 loops=1)

  • Buffers: shared hit=213108954, local hit=455
2. 15,714.348 79,705.378 ↓ 17,725,571.0 17,725,571 1

Nested Loop (cost=3,275.24..6,379.47 rows=1 width=329) (actual time=29.363..79,705.378 rows=17,725,571 loops=1)

  • Buffers: shared hit=88652574, local hit=455
3. 3,313.560 46,265.459 ↓ 17,725,571.0 17,725,571 1

Nested Loop (cost=3,274.96..6,375.16 rows=1 width=333) (actual time=29.356..46,265.459 rows=17,725,571 loops=1)

  • Buffers: shared hit=53201431, local hit=455
4. 5,275.564 7,500.757 ↓ 17,725,571.0 17,725,571 1

Merge Join (cost=3,274.69..6,366.86 rows=1 width=299) (actual time=29.349..7,500.757 rows=17,725,571 loops=1)

  • Buffers: shared hit=24718, local hit=455
5. 10.887 13.063 ↓ 2.7 10,593 1

Sort (cost=442.29..452.25 rows=3,984 width=276) (actual time=5.02..13.063 rows=10,593 loops=1)

  • Sort Key: d.condition
  • Sort Method: quicksort Memory: 1874kB
  • Buffers: local hit=154
6. 2.176 2.176 ↓ 2.7 10,593 1

Seq Scan on GPIclassAdhMap d (cost=0..204.05 rows=3,984 width=276) (actual time=0.007..2.176 rows=10,593 loops=1)

  • Filter: (d.condition <> 'Diabetes'::text)
  • Buffers: local hit=154
7. 2,189.561 2,212.130 ↓ 1,723.4 17,719,695 1

Sort (cost=2,832.39..2,858.1 rows=10,282 width=55) (actual time=24.293..2,212.13 rows=17,719,695 loops=1)

  • Sort Key: c."DiseaseState"
  • Sort Method: quicksort Memory: 579kB
  • Buffers: shared hit=24718, local hit=301
8. 3.068 22.569 ↑ 2.1 4,942 1

Nested Loop (cost=333.17..2,147.21 rows=10,282 width=55) (actual time=3.904..22.569 rows=4,942 loops=1)

  • Buffers: shared hit=24718, local hit=301
9. 3.448 4.675 ↓ 23.4 4,942 1

Aggregate (cost=332.61..334.72 rows=211 width=36) (actual time=3.891..4.675 rows=4,942 loops=1)

  • Buffers: local hit=301
10. 1.227 1.227 ↓ 4.2 8,776 1

Seq Scan on patient_tip_claim c (cost=0..322.07 rows=2,107 width=36) (actual time=0.005..1.227 rows=8,776 loops=1)

  • Buffers: local hit=301
11. 14.826 14.826 ↑ 1.0 1 4,942

Index Scan using patient_pkey on patient pt (cost=0.56..8.58 rows=1 width=19) (actual time=0.003..0.003 rows=1 loops=4,942)

  • Index Cond: (pt."PatientID" = c.patientid)
  • Filter: (pt."PolicyID" = 1023)
  • Buffers: shared hit=24718
12. 35,451.142 35,451.142 ↑ 1.0 1 17,725,571

Index Scan using "Policy_pkey" on Policy po (cost=0.28..8.29 rows=1 width=34) (actual time=0.001..0.002 rows=1 loops=17,725,571)

  • Index Cond: (po."policyID" = 1023)
  • Buffers: shared hit=53176713
13. 17,725.571 17,725.571 ↑ 1.0 1 17,725,571

Index Only Scan using "policyRxDate_pkey" on policyRxDate plrx (cost=0.28..4.29 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=17,725,571)

  • Index Cond: (plrx.policyid = 1023)
  • Buffers: shared hit=35451143
14. 407,688.133 407,688.133 ↓ 0.0 0 17,725,571

Index Scan using ind_patiendid_rxdate_active_changedate on prescription rx (cost=0.7..24.99 rows=1 width=28) (actual time=0.023..0.023 rows=0 loops=17,725,571)

  • Index Cond: ((rx.patientid = pt."PatientID") AND ((rx."NDC")::text = (d."NDC")::text))
  • Filter: (date_part('year'::text, (rx."RxDate")::timestamp without time zone) = ANY ('{2017,2018,2019}'::double precision[]))
  • Buffers: shared hit=124456380