explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0bTn

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Gather Merge (cost=292,151,764.09..327,266,594.85 rows=300,963,250 width=233) (actual rows= loops=)

  • Workers Planned: 2
2. 0.000 0.000 ↓ 0.0

Sort (cost=292,150,764.06..292,526,968.13 rows=150,481,625 width=233) (actual rows= loops=)

  • Sort Key: (count(*)) DESC
3. 0.000 0.000 ↓ 0.0

Merge Join (cost=105,842,360.62..203,818,504.25 rows=150,481,625 width=233) (actual rows= loops=)

  • Merge Cond: ((nr.npi)::text = ((unnest(ARRAY[CASE WHEN ((n.npi_type)::text = '1'::text) THEN sl.provider_billing_npi ELSE NULL::character varying END, CASE WHEN ((CASE WHEN ((n1.npi_type)::text = '1'::text) THEN sl.provider_rendering_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n.npi_type)::text = '1'::text) THEN sl.provider_billing_npi ELSE NULL::character varying END)::text) THEN CASE WHEN ((n1.npi_type)::text = '1'::text) THEN sl.provider_rendering_npi ELSE NULL::character varying END ELSE NULL::character varying END, CASE WHEN (((CASE WHEN ((n2.npi_type)::text = '1'::text) THEN sl.provider_referring_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n.npi_type)::text = '1'::text) THEN sl.provider_billing_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n2.npi_type)::text = '1'::text) THEN sl.provider_referring_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n1.npi_type)::text = '1'::text) THEN sl.provider_rendering_npi ELSE NULL::character varying END)::text)) THEN CASE WHEN ((n2.npi_type)::text = '1'::text) THEN sl.provider_referring_npi ELSE NULL::character varying END ELSE NULL::character varying END, CASE WHEN (((CASE WHEN ((n3.npi_type)::text = '1'::text) THEN sl.provider_facility_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n.npi_type)::text = '1'::text) THEN sl.provider_billing_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n3.npi_type)::text = '1'::text) THEN sl.provider_facility_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n1.npi_type)::text = '1'::text) THEN sl.provider_rendering_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n3.npi_type)::text = '1'::text) THEN sl.provider_facility_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n2.npi_type)::text = '1'::text) THEN sl.provider_referring_npi ELSE NULL::character varying END)::text)) THEN CASE WHEN ((n3.npi_type)::text = '1'::text) THEN sl.provider_facility_npi ELSE NULL::character varying END ELSE NULL::character varying END, CASE WHEN (((CASE WHEN ((n5.npi_type)::text = '1'::text) THEN sl.provider_purch_svc_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n.npi_type)::text = '1'::text) THEN sl.provider_billing_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n5.npi_type)::text = '1'::text) THEN sl.provider_purch_svc_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n1.npi_type)::text = '1'::text) THEN sl.provider_rendering_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n5.npi_type)::text = '1'::text) THEN sl.provider_purch_svc_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n2.npi_type)::text = '1'::text) THEN sl.provider_referring_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n5.npi_type)::text = '1'::text) THEN sl.provider_purch_svc_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n3.npi_type)::text = '1'::text) THEN sl.provider_facility_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n5.npi_type)::text = '1'::text) THEN sl.provider_purch_svc_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n7.npi_type)::text = '1'::text) THEN sl.provider_pay_to_npi ELSE NULL::character varying END)::text)) THEN CASE WHEN ((n5.npi_type)::text = '1'::text) THEN sl.provider_purch_svc_npi ELSE NULL::character varying END ELSE NULL::character varying END, CASE WHEN (((CASE WHEN ((n6.npi_type)::text = '1'::text) THEN sl.provider_supervising_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n.npi_type)::text = '1'::text) THEN sl.provider_billing_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n6.npi_type)::text = '1'::text) THEN sl.provider_supervising_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n1.npi_type)::text = '1'::text) THEN sl.provider_rendering_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n6.npi_type)::text = '1'::text) THEN sl.provider_supervising_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n2.npi_type)::text = '1'::text) THEN sl.provider_referring_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n6.npi_type)::text = '1'::text) THEN sl.provider_supervising_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n3.npi_type)::text = '1'::text) THEN sl.provider_facility_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n6.npi_type)::text = '1'::text) THEN sl.provider_supervising_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n7.npi_type)::text = '1'::text) THEN sl.provider_pay_to_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n6.npi_type)::text = '1'::text) THEN sl.provider_supervising_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n5.npi_type)::text = '1'::text) THEN sl.provider_purch_svc_npi ELSE NULL::character varying END)::text)) THEN CASE WHEN ((n6.npi_type)::text = '1'::text) THEN sl.provider_supervising_npi ELSE NULL::character varying END ELSE NULL::character varying END, CASE WHEN (((CASE WHEN ((n7.npi_type)::text = '1'::text) THEN sl.provider_pay_to_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n.npi_type)::text = '1'::text) THEN sl.provider_billing_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n7.npi_type)::text = '1'::text) THEN sl.provider_pay_to_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n1.npi_type)::text = '1'::text) THEN sl.provider_rendering_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n7.npi_type)::text = '1'::text) THEN sl.provider_pay_to_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n2.npi_type)::text = '1'::text) THEN sl.provider_referring_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n7.npi_type)::text = '1'::text) THEN sl.provider_pay_to_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n3.npi_type)::text = '1'::text) THEN sl.provider_facility_npi ELSE NULL::character varying END)::text)) THEN CASE WHEN ((n7.npi_type)::text = '1'::text) THEN sl.provider_pay_to_npi ELSE NULL::character varying END ELSE NULL::character varying END, CASE WHEN (((CASE WHEN ((n4.npi_type)::text = '1'::text) THEN sl.provider_ordering_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n.npi_type)::text = '1'::text) THEN sl.provider_billing_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n4.npi_type)::text = '1'::text) THEN sl.provider_ordering_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n1.npi_type)::text = '1'::text) THEN sl.provider_rendering_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n4.npi_type)::text = '1'::text) THEN sl.provider_ordering_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n2.npi_type)::text = '1'::text) THEN sl.provider_referring_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n4.npi_type)::text = '1'::text) THEN sl.provider_ordering_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n3.npi_type)::text = '1'::text) THEN sl.provider_facility_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n4.npi_type)::text = '1'::text) THEN sl.provider_ordering_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n7.npi_type)::text = '1'::text) THEN sl.provider_pay_to_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n4.npi_type)::text = '1'::text) THEN sl.provider_ordering_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n5.npi_type)::text = '1'::text) THEN sl.provider_purch_svc_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n4.npi_type)::text = '1'::text) THEN sl.provider_ordering_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n6.npi_type)::text = '1'::text) THEN sl.provider_supervising_npi ELSE NULL::character varying END)::text)) THEN CASE WHEN ((n4.npi_type)::text = '1'::text) THEN sl.provider_ordering_npi ELSE NULL::character varying END ELSE NULL::character varying END])))::text)
4. 0.000 0.000 ↓ 0.0

Sort (cost=742,555.05..748,858.05 rows=2,521,200 width=117) (actual rows= loops=)

  • Sort Key: nr.npi
5. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=32.40..164,253.90 rows=2,521,200 width=117) (actual rows= loops=)

  • Hash Cond: ((nr.taxonomy)::text = (t.code)::text)
6. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on npi_registry nr (cost=0.00..129,555.00 rows=2,521,200 width=92) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Hash (cost=21.62..21.62 rows=862 width=46) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Seq Scan on taxonomy t (cost=0.00..21.62 rows=862 width=46) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Materialize (cost=105,099,805.57..199,903,229.32 rows=361,155,900 width=40) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=105,099,805.57..195,388,780.57 rows=361,155,900 width=40) (actual rows= loops=)

  • Group Key: (unnest(ARRAY[CASE WHEN ((n.npi_type)::text = '1'::text) THEN sl.provider_billing_npi ELSE NULL::character varying END, CASE WHEN ((CASE WHEN ((n1.npi_type)::text = '1'::text) THEN sl.provider_rendering_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n.npi_type)::text = '1'::text) THEN sl.provider_billing_npi ELSE NULL::character varying END)::text) THEN CASE WHEN ((n1.npi_type)::text = '1'::text) THEN sl.provider_rendering_npi ELSE NULL::character varying END ELSE NULL::character varying END, CASE WHEN (((CASE WHEN ((n2.npi_type)::text = '1'::text) THEN sl.provider_referring_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n.npi_type)::text = '1'::text) THEN sl.provider_billing_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n2.npi_type)::text = '1'::text) THEN sl.provider_referring_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n1.npi_type)::text = '1'::text) THEN sl.provider_rendering_npi ELSE NULL::character varying END)::text)) THEN CASE WHEN ((n2.npi_type)::text = '1'::text) THEN sl.provider_referring_npi ELSE NULL::character varying END ELSE NULL::character varying END, CASE WHEN (((CASE WHEN ((n3.npi_type)::text = '1'::text) THEN sl.provider_facility_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n.npi_type)::text = '1'::text) THEN sl.provider_billing_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n3.npi_type)::text = '1'::text) THEN sl.provider_facility_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n1.npi_type)::text = '1'::text) THEN sl.provider_rendering_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n3.npi_type)::text = '1'::text) THEN sl.provider_facility_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n2.npi_type)::text = '1'::text) THEN sl.provider_referring_npi ELSE NULL::character varying END)::text)) THEN CASE WHEN ((n3.npi_type)::text = '1'::text) THEN sl.provider_facility_npi ELSE NULL::character varying END ELSE NULL::character varying END, CASE WHEN (((CASE WHEN ((n5.npi_type)::text = '1'::text) THEN sl.provider_purch_svc_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n.npi_type)::text = '1'::text) THEN sl.provider_billing_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n5.npi_type)::text = '1'::text) THEN sl.provider_purch_svc_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n1.npi_type)::text = '1'::text) THEN sl.provider_rendering_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n5.npi_type)::text = '1'::text) THEN sl.provider_purch_svc_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n2.npi_type)::text = '1'::text) THEN sl.provider_referring_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n5.npi_type)::text = '1'::text) THEN sl.provider_purch_svc_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n3.npi_type)::text = '1'::text) THEN sl.provider_facility_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n5.npi_type)::text = '1'::text) THEN sl.provider_purch_svc_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n7.npi_type)::text = '1'::text) THEN sl.provider_pay_to_npi ELSE NULL::character varying END)::text)) THEN CASE WHEN ((n5.npi_type)::text = '1'::text) THEN sl.provider_purch_svc_npi ELSE NULL::character varying END ELSE NULL::character varying END, CASE WHEN (((CASE WHEN ((n6.npi_type)::text = '1'::text) THEN sl.provider_supervising_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n.npi_type)::text = '1'::text) THEN sl.provider_billing_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n6.npi_type)::text = '1'::text) THEN sl.provider_supervising_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n1.npi_type)::text = '1'::text) THEN sl.provider_rendering_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n6.npi_type)::text = '1'::text) THEN sl.provider_supervising_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n2.npi_type)::text = '1'::text) THEN sl.provider_referring_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n6.npi_type)::text = '1'::text) THEN sl.provider_supervising_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n3.npi_type)::text = '1'::text) THEN sl.provider_facility_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n6.npi_type)::text = '1'::text) THEN sl.provider_supervising_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n7.npi_type)::text = '1'::text) THEN sl.provider_pay_to_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n6.npi_type)::text = '1'::text) THEN sl.provider_supervising_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n5.npi_type)::text = '1'::text) THEN sl.provider_purch_svc_npi ELSE NULL::character varying END)::text)) THEN CASE WHEN ((n6.npi_type)::text = '1'::text) THEN sl.provider_supervising_npi ELSE NULL::character varying END ELSE NULL::character varying END, CASE WHEN (((CASE WHEN ((n7.npi_type)::text = '1'::text) THEN sl.provider_pay_to_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n.npi_type)::text = '1'::text) THEN sl.provider_billing_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n7.npi_type)::text = '1'::text) THEN sl.provider_pay_to_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n1.npi_type)::text = '1'::text) THEN sl.provider_rendering_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n7.npi_type)::text = '1'::text) THEN sl.provider_pay_to_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n2.npi_type)::text = '1'::text) THEN sl.provider_referring_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n7.npi_type)::text = '1'::text) THEN sl.provider_pay_to_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n3.npi_type)::text = '1'::text) THEN sl.provider_facility_npi ELSE NULL::character varying END)::text)) THEN CASE WHEN ((n7.npi_type)::text = '1'::text) THEN sl.provider_pay_to_npi ELSE NULL::character varying END ELSE NULL::character varying END, CASE WHEN (((CASE WHEN ((n4.npi_type)::text = '1'::text) THEN sl.provider_ordering_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n.npi_type)::text = '1'::text) THEN sl.provider_billing_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n4.npi_type)::text = '1'::text) THEN sl.provider_ordering_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n1.npi_type)::text = '1'::text) THEN sl.provider_rendering_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n4.npi_type)::text = '1'::text) THEN sl.provider_ordering_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n2.npi_type)::text = '1'::text) THEN sl.provider_referring_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n4.npi_type)::text = '1'::text) THEN sl.provider_ordering_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n3.npi_type)::text = '1'::text) THEN sl.provider_facility_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n4.npi_type)::text = '1'::text) THEN sl.provider_ordering_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n7.npi_type)::text = '1'::text) THEN sl.provider_pay_to_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n4.npi_type)::text = '1'::text) THEN sl.provider_ordering_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n5.npi_type)::text = '1'::text) THEN sl.provider_purch_svc_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n4.npi_type)::text = '1'::text) THEN sl.provider_ordering_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n6.npi_type)::text = '1'::text) THEN sl.provider_supervising_npi ELSE NULL::character varying END)::text)) THEN CASE WHEN ((n4.npi_type)::text = '1'::text) THEN sl.provider_ordering_npi ELSE NULL::character varying END ELSE NULL::character varying END]))
11. 0.000 0.000 ↓ 0.0

Sort (cost=105,099,805.57..106,002,695.32 rows=361,155,900 width=32) (actual rows= loops=)

  • Sort Key: (unnest(ARRAY[CASE WHEN ((n.npi_type)::text = '1'::text) THEN sl.provider_billing_npi ELSE NULL::character varying END, CASE WHEN ((CASE WHEN ((n1.npi_type)::text = '1'::text) THEN sl.provider_rendering_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n.npi_type)::text = '1'::text) THEN sl.provider_billing_npi ELSE NULL::character varying END)::text) THEN CASE WHEN ((n1.npi_type)::text = '1'::text) THEN sl.provider_rendering_npi ELSE NULL::character varying END ELSE NULL::character varying END, CASE WHEN (((CASE WHEN ((n2.npi_type)::text = '1'::text) THEN sl.provider_referring_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n.npi_type)::text = '1'::text) THEN sl.provider_billing_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n2.npi_type)::text = '1'::text) THEN sl.provider_referring_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n1.npi_type)::text = '1'::text) THEN sl.provider_rendering_npi ELSE NULL::character varying END)::text)) THEN CASE WHEN ((n2.npi_type)::text = '1'::text) THEN sl.provider_referring_npi ELSE NULL::character varying END ELSE NULL::character varying END, CASE WHEN (((CASE WHEN ((n3.npi_type)::text = '1'::text) THEN sl.provider_facility_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n.npi_type)::text = '1'::text) THEN sl.provider_billing_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n3.npi_type)::text = '1'::text) THEN sl.provider_facility_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n1.npi_type)::text = '1'::text) THEN sl.provider_rendering_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n3.npi_type)::text = '1'::text) THEN sl.provider_facility_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n2.npi_type)::text = '1'::text) THEN sl.provider_referring_npi ELSE NULL::character varying END)::text)) THEN CASE WHEN ((n3.npi_type)::text = '1'::text) THEN sl.provider_facility_npi ELSE NULL::character varying END ELSE NULL::character varying END, CASE WHEN (((CASE WHEN ((n5.npi_type)::text = '1'::text) THEN sl.provider_purch_svc_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n.npi_type)::text = '1'::text) THEN sl.provider_billing_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n5.npi_type)::text = '1'::text) THEN sl.provider_purch_svc_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n1.npi_type)::text = '1'::text) THEN sl.provider_rendering_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n5.npi_type)::text = '1'::text) THEN sl.provider_purch_svc_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n2.npi_type)::text = '1'::text) THEN sl.provider_referring_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n5.npi_type)::text = '1'::text) THEN sl.provider_purch_svc_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n3.npi_type)::text = '1'::text) THEN sl.provider_facility_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n5.npi_type)::text = '1'::text) THEN sl.provider_purch_svc_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n7.npi_type)::text = '1'::text) THEN sl.provider_pay_to_npi ELSE NULL::character varying END)::text)) THEN CASE WHEN ((n5.npi_type)::text = '1'::text) THEN sl.provider_purch_svc_npi ELSE NULL::character varying END ELSE NULL::character varying END, CASE WHEN (((CASE WHEN ((n6.npi_type)::text = '1'::text) THEN sl.provider_supervising_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n.npi_type)::text = '1'::text) THEN sl.provider_billing_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n6.npi_type)::text = '1'::text) THEN sl.provider_supervising_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n1.npi_type)::text = '1'::text) THEN sl.provider_rendering_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n6.npi_type)::text = '1'::text) THEN sl.provider_supervising_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n2.npi_type)::text = '1'::text) THEN sl.provider_referring_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n6.npi_type)::text = '1'::text) THEN sl.provider_supervising_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n3.npi_type)::text = '1'::text) THEN sl.provider_facility_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n6.npi_type)::text = '1'::text) THEN sl.provider_supervising_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n7.npi_type)::text = '1'::text) THEN sl.provider_pay_to_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n6.npi_type)::text = '1'::text) THEN sl.provider_supervising_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n5.npi_type)::text = '1'::text) THEN sl.provider_purch_svc_npi ELSE NULL::character varying END)::text)) THEN CASE WHEN ((n6.npi_type)::text = '1'::text) THEN sl.provider_supervising_npi ELSE NULL::character varying END ELSE NULL::character varying END, CASE WHEN (((CASE WHEN ((n7.npi_type)::text = '1'::text) THEN sl.provider_pay_to_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n.npi_type)::text = '1'::text) THEN sl.provider_billing_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n7.npi_type)::text = '1'::text) THEN sl.provider_pay_to_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n1.npi_type)::text = '1'::text) THEN sl.provider_rendering_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n7.npi_type)::text = '1'::text) THEN sl.provider_pay_to_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n2.npi_type)::text = '1'::text) THEN sl.provider_referring_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n7.npi_type)::text = '1'::text) THEN sl.provider_pay_to_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n3.npi_type)::text = '1'::text) THEN sl.provider_facility_npi ELSE NULL::character varying END)::text)) THEN CASE WHEN ((n7.npi_type)::text = '1'::text) THEN sl.provider_pay_to_npi ELSE NULL::character varying END ELSE NULL::character varying END, CASE WHEN (((CASE WHEN ((n4.npi_type)::text = '1'::text) THEN sl.provider_ordering_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n.npi_type)::text = '1'::text) THEN sl.provider_billing_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n4.npi_type)::text = '1'::text) THEN sl.provider_ordering_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n1.npi_type)::text = '1'::text) THEN sl.provider_rendering_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n4.npi_type)::text = '1'::text) THEN sl.provider_ordering_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n2.npi_type)::text = '1'::text) THEN sl.provider_referring_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n4.npi_type)::text = '1'::text) THEN sl.provider_ordering_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n3.npi_type)::text = '1'::text) THEN sl.provider_facility_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n4.npi_type)::text = '1'::text) THEN sl.provider_ordering_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n7.npi_type)::text = '1'::text) THEN sl.provider_pay_to_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n4.npi_type)::text = '1'::text) THEN sl.provider_ordering_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n5.npi_type)::text = '1'::text) THEN sl.provider_purch_svc_npi ELSE NULL::character varying END)::text) AND ((CASE WHEN ((n4.npi_type)::text = '1'::text) THEN sl.provider_ordering_npi ELSE NULL::character varying END)::text IS DISTINCT FROM (CASE WHEN ((n6.npi_type)::text = '1'::text) THEN sl.provider_supervising_npi ELSE NULL::character varying END)::text)) THEN CASE WHEN ((n4.npi_type)::text = '1'::text) THEN sl.provider_ordering_npi ELSE NULL::character varying END ELSE NULL::character varying END]))
12. 0.000 0.000 ↓ 0.0

ProjectSet (cost=1,769,621.66..19,201,261.98 rows=361,155,900 width=32) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,769,621.66..16,537,737.22 rows=3,611,559 width=35) (actual rows= loops=)

  • Hash Cond: ((sl.provider_pay_to_npi)::text = (n7.npi)::text)
  • Filter: (((n.npi_type)::text = '1'::text) OR ((n1.npi_type)::text = '1'::text) OR ((n2.npi_type)::text = '1'::text) OR ((n3.npi_type)::text = '1'::text) OR ((n4.npi_type)::text = '1'::text) OR ((n5.npi_type)::text = '1'::text) OR ((n6.npi_type)::text = '1'::text) OR ((n7.npi_type)::text = '1'::text))
14. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,549,169.84..16,086,843.16 rows=3,611,606 width=34) (actual rows= loops=)

  • Hash Cond: ((sl.provider_supervising_npi)::text = (n6.npi)::text)
15. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,328,718.02..15,708,181.21 rows=3,611,606 width=33) (actual rows= loops=)

  • Hash Cond: ((sl.provider_purch_svc_npi)::text = (n5.npi)::text)
16. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,108,266.19..15,336,573.27 rows=3,611,606 width=32) (actual rows= loops=)

  • Hash Cond: ((sl.provider_ordering_npi)::text = (n4.npi)::text)
17. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=887,814.37..14,964,965.33 rows=3,611,606 width=31) (actual rows= loops=)

  • Hash Cond: ((sl.provider_facility_npi)::text = (n3.npi)::text)
18. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=667,362.55..14,593,357.39 rows=3,611,606 width=30) (actual rows= loops=)

  • Hash Cond: ((sl.provider_referring_npi)::text = (n2.npi)::text)
19. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=446,910.73..14,221,749.44 rows=3,611,606 width=29) (actual rows= loops=)

  • Hash Cond: ((sl.provider_rendering_npi)::text = (n1.npi)::text)
20. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=226,458.90..13,850,141.50 rows=3,611,606 width=28) (actual rows= loops=)

  • Hash Cond: ((sl.provider_billing_npi)::text = (n.npi)::text)
21. 0.000 0.000 ↓ 0.0

Nested Loop (cost=6,007.08..13,478,533.56 rows=3,611,606 width=27) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Seq Scan on bucketsandcodes bac (cost=0.00..56.95 rows=13 width=6) (actual rows= loops=)

  • Filter: ((bucketname)::text = (bucketname)::text)
23. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on service_lines2018 sl (cost=6,007.08..1,033,974.78 rows=283,111 width=32) (actual rows= loops=)

  • Recheck Cond: (((dx_code_position)::text = '1'::text) AND ((procedure_cd)::text = (bac.hcpccode)::text))
24. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on procedure_dx_code_postion (cost=0.00..5,936.30 rows=283,111 width=0) (actual rows= loops=)

  • Index Cond: (((dx_code_position)::text = '1'::text) AND ((procedure_cd)::text = (bac.hcpccode)::text))
25. 0.000 0.000 ↓ 0.0

Hash (cost=115,269.81..115,269.81 rows=6,050,881 width=12) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Seq Scan on npi_entities n (cost=0.00..115,269.81 rows=6,050,881 width=12) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Hash (cost=115,269.81..115,269.81 rows=6,050,881 width=12) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Seq Scan on npi_entities n1 (cost=0.00..115,269.81 rows=6,050,881 width=12) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Hash (cost=115,269.81..115,269.81 rows=6,050,881 width=12) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Seq Scan on npi_entities n2 (cost=0.00..115,269.81 rows=6,050,881 width=12) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Hash (cost=115,269.81..115,269.81 rows=6,050,881 width=12) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Seq Scan on npi_entities n3 (cost=0.00..115,269.81 rows=6,050,881 width=12) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Hash (cost=115,269.81..115,269.81 rows=6,050,881 width=12) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Seq Scan on npi_entities n4 (cost=0.00..115,269.81 rows=6,050,881 width=12) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Hash (cost=115,269.81..115,269.81 rows=6,050,881 width=12) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Seq Scan on npi_entities n5 (cost=0.00..115,269.81 rows=6,050,881 width=12) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Hash (cost=115,269.81..115,269.81 rows=6,050,881 width=12) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Seq Scan on npi_entities n6 (cost=0.00..115,269.81 rows=6,050,881 width=12) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Hash (cost=115,269.81..115,269.81 rows=6,050,881 width=12) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Seq Scan on npi_entities n7 (cost=0.00..115,269.81 rows=6,050,881 width=12) (actual rows= loops=)