explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RMDK

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

Sort (cost=16,501,737.70..16,501,738.20 rows=200 width=192) (actual rows= loops=)

  • Sort Key: px.org, px.count DESC
2.          

CTE basequery

3. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,769,621.66..16,501,637.36 rows=90 width=512) (actual rows= loops=)

  • Hash Cond: ((sl.provider_pay_to_npi)::text = (n7.npi)::text)
  • Filter: (((LEAST(n.npi_type, n1.npi_type, n2.npi_type, n3.npi_type, n4.npi_type, n5.npi_type, n6.npi_type, n7.npi_type))::text = '1'::text) AND ((GREATEST(n.npi_type, n1.npi_type, n2.npi_type, n3.npi_type, n4.npi_type, n5.npi_type, n6.npi_type, n7.npi_type))::text = '2'::text))
4. 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)
5. 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)
6. 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)
7. 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)
8. 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)
9. 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)
10. 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)
11. 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=)

12. 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)
13. 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))
14. 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))
15. 0.000 0.000 ↓ 0.0

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

16. 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=)

17. 0.000 0.000 ↓ 0.0

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

18. 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=)

19. 0.000 0.000 ↓ 0.0

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

20. 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=)

21. 0.000 0.000 ↓ 0.0

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

22. 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=)

23. 0.000 0.000 ↓ 0.0

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

24. 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=)

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 n5 (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 n6 (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 n7 (cost=0.00..115,269.81 rows=6,050,881 width=12) (actual rows= loops=)

31.          

CTE pxdx

32. 0.000 0.000 ↓ 0.0

HashAggregate (cost=29.70..32.20 rows=200 width=96) (actual rows= loops=)

  • Group Key: basequery.org1, basequery.individual2
33. 0.000 0.000 ↓ 0.0

Append (cost=2.48..25.65 rows=540 width=72) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

HashAggregate (cost=2.48..3.38 rows=90 width=72) (actual rows= loops=)

  • Group Key: basequery.org1, basequery.individual2
35. 0.000 0.000 ↓ 0.0

CTE Scan on basequery (cost=0.00..1.80 rows=90 width=64) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

HashAggregate (cost=2.48..3.38 rows=90 width=72) (actual rows= loops=)

  • Group Key: basequery_1.org1, basequery_1.individual3
37. 0.000 0.000 ↓ 0.0

CTE Scan on basequery basequery_1 (cost=0.00..1.80 rows=90 width=64) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

HashAggregate (cost=2.48..3.38 rows=90 width=72) (actual rows= loops=)

  • Group Key: basequery_2.org2, basequery_2.individual1
39. 0.000 0.000 ↓ 0.0

CTE Scan on basequery basequery_2 (cost=0.00..1.80 rows=90 width=64) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

HashAggregate (cost=2.48..3.38 rows=90 width=72) (actual rows= loops=)

  • Group Key: basequery_3.org2, basequery_3.individual3
41. 0.000 0.000 ↓ 0.0

CTE Scan on basequery basequery_3 (cost=0.00..1.80 rows=90 width=64) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

HashAggregate (cost=2.48..3.38 rows=90 width=72) (actual rows= loops=)

  • Group Key: basequery_4.org3, basequery_4.individual1
43. 0.000 0.000 ↓ 0.0

CTE Scan on basequery basequery_4 (cost=0.00..1.80 rows=90 width=64) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

HashAggregate (cost=2.48..3.38 rows=90 width=72) (actual rows= loops=)

  • Group Key: basequery_5.org3, basequery_5.individual2
45. 0.000 0.000 ↓ 0.0

CTE Scan on basequery basequery_5 (cost=0.00..1.80 rows=90 width=64) (actual rows= loops=)

46.          

CTE cte

47. 0.000 0.000 ↓ 0.0

HashAggregate (cost=5.00..7.50 rows=200 width=64) (actual rows= loops=)

  • Group Key: pxdx.individual
48. 0.000 0.000 ↓ 0.0

CTE Scan on pxdx (cost=0.00..4.00 rows=200 width=64) (actual rows= loops=)

49.          

CTE xyz

50. 0.000 0.000 ↓ 0.0

HashAggregate (cost=5.00..7.50 rows=200 width=64) (actual rows= loops=)

  • Group Key: pxdx_1.org
51. 0.000 0.000 ↓ 0.0

CTE Scan on pxdx pxdx_1 (cost=0.00..4.00 rows=200 width=64) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Hash Join (cost=13.00..45.50 rows=200 width=192) (actual rows= loops=)

  • Hash Cond: ((px.org)::text = (xyz.npi)::text)
53. 0.000 0.000 ↓ 0.0

Hash Join (cost=6.50..18.00 rows=200 width=128) (actual rows= loops=)

  • Hash Cond: ((px.individual)::text = (cte.individual)::text)
54. 0.000 0.000 ↓ 0.0

CTE Scan on pxdx px (cost=0.00..4.00 rows=200 width=96) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Hash (cost=4.00..4.00 rows=200 width=64) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

CTE Scan on cte (cost=0.00..4.00 rows=200 width=64) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Hash (cost=4.00..4.00 rows=200 width=64) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

CTE Scan on xyz (cost=0.00..4.00 rows=200 width=64) (actual rows= loops=)