explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9qER

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

HashAggregate (cost=814,591,226,031,194.75..814,591,226,031,197.25 rows=200 width=24) (actual rows= loops=)

  • Group Key: promises_kept.promised_date
2.          

CTE set_shipments

3. 0.000 0.000 ↓ 0.0

Unique (cost=542,577.17..542,729.32 rows=5,478 width=41) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Sort (cost=542,577.17..542,653.24 rows=30,430 width=41) (actual rows= loops=)

  • Sort Key: deliveries.shipment_id, deliveries.date
5. 0.000 0.000 ↓ 0.0

Merge Join (cost=537,736.80..540,311.16 rows=30,430 width=41) (actual rows= loops=)

  • Merge Cond: (u.id = deliveries.user_id)
6. 0.000 0.000 ↓ 0.0

Sort (cost=456,855.81..457,170.75 rows=125,979 width=1,677) (actual rows= loops=)

  • Sort Key: u.id DESC
7. 0.000 0.000 ↓ 0.0

Subquery Scan on u (cost=443,664.03..446,183.61 rows=125,979 width=1,677) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

HashAggregate (cost=443,664.03..444,923.82 rows=125,979 width=1,645) (actual rows= loops=)

  • Group Key: u_1.id
9. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=286,772.29..424,053.89 rows=7,844,053 width=4) (actual rows= loops=)

  • Merge Cond: (disease_state_ndc9s.ndc9 = ("substring"((rx.sku)::text, 1, 9)))
10. 0.000 0.000 ↓ 0.0

Sort (cost=142.12..145.68 rows=1,423 width=32) (actual rows= loops=)

  • Sort Key: disease_state_ndc9s.ndc9
11. 0.000 0.000 ↓ 0.0

Subquery Scan on disease_state_ndc9s (cost=39.13..67.59 rows=1,423 width=32) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

HashAggregate (cost=39.13..53.36 rows=1,423 width=64) (actual rows= loops=)

  • Group Key: ('Infectious Disease'::text), "*VALUES*".column1
13. 0.000 0.000 ↓ 0.0

Append (cost=0.00..32.02 rows=1,423 width=64) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Values Scan on "*VALUES*" (cost=0.00..3.56 rows=285 width=64) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Values Scan on "*VALUES*_1" (cost=0.00..2.78 rows=222 width=64) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Values Scan on "*VALUES*_2" (cost=0.00..0.53 rows=42 width=64) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Values Scan on "*VALUES*_3" (cost=0.00..0.44 rows=35 width=64) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Values Scan on "*VALUES*_4" (cost=0.00..0.03 rows=2 width=64) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Values Scan on "*VALUES*_5" (cost=0.00..0.64 rows=51 width=64) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Values Scan on "*VALUES*_6" (cost=0.00..0.30 rows=24 width=64) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Values Scan on "*VALUES*_7" (cost=0.00..0.34 rows=27 width=64) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Values Scan on "*VALUES*_8" (cost=0.00..0.81 rows=65 width=64) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Values Scan on "*VALUES*_9" (cost=0.00..0.48 rows=38 width=64) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Values Scan on "*VALUES*_10" (cost=0.00..0.83 rows=66 width=64) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Values Scan on "*VALUES*_11" (cost=0.00..2.46 rows=197 width=64) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Values Scan on "*VALUES*_12" (cost=0.00..2.86 rows=229 width=64) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Values Scan on "*VALUES*_13" (cost=0.00..0.04 rows=3 width=64) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Values Scan on "*VALUES*_14" (cost=0.00..1.71 rows=137 width=64) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Sort (cost=286,630.17..289,386.34 rows=1,102,467 width=15) (actual rows= loops=)

  • Sort Key: ("substring"((rx.sku)::text, 1, 9))
30. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=112,950.49..175,984.91 rows=1,102,467 width=15) (actual rows= loops=)

  • Hash Cond: (rx.clinics_doctor_id = clinics_doctors.id)
31. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=79,230.25..127,117.88 rows=1,102,467 width=19) (actual rows= loops=)

  • Hash Cond: (rx.user_id = u_1.id)
32. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=72,782.89..117,776.41 rows=1,102,477 width=19) (actual rows= loops=)

  • Hash Cond: (del.prescription_id = rx.id)
33. 0.000 0.000 ↓ 0.0

Index Only Scan using index_deliveries_on_prescription_id on deliveries del (cost=0.43..42,088.70 rows=1,106,756 width=4) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Hash (cost=65,502.76..65,502.76 rows=582,376 width=23) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Seq Scan on prescriptions rx (cost=0.00..65,502.76 rows=582,376 width=23) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Hash (cost=4,872.62..4,872.62 rows=125,979 width=4) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Index Only Scan using users_pkey on users u_1 (cost=0.42..4,872.62 rows=125,979 width=4) (actual rows= loops=)

  • Filter: (id <> 1058)
38. 0.000 0.000 ↓ 0.0

Hash (cost=33,376.53..33,376.53 rows=27,497 width=4) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=32,347.47..33,376.53 rows=27,497 width=4) (actual rows= loops=)

  • Hash Cond: (clinics_doctors.clinic_id = clinics.id)
40. 0.000 0.000 ↓ 0.0

Seq Scan on clinics_doctors (cost=0.00..650.97 rows=27,497 width=8) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Hash (cost=17,911.22..17,911.22 rows=1,154,900 width=4) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Subquery Scan on clinics (cost=0.00..17,911.22 rows=1,154,900 width=4) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..6,362.23 rows=1,154,900 width=751) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Seq Scan on clinics clinics_1 (cost=0.00..414.49 rows=11,549 width=25) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Sort (cost=80,881.00..80,894.80 rows=5,522 width=37) (actual rows= loops=)

  • Sort Key: deliveries.user_id DESC
46. 0.000 0.000 ↓ 0.0

Gather (cost=1,000.00..80,537.78 rows=5,522 width=37) (actual rows= loops=)

  • Workers Planned: 2
47. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on deliveries (cost=0.00..78,985.58 rows=2,301 width=37) (actual rows= loops=)

  • Filter: (((status)::text <> 'upcoming'::text) AND (date_trunc('week'::text, (date)::timestamp with time zone) >= '2019-08-31 00:00:00+00'::timestamp with time zone) AND (date_trunc('week'::text, (date)::timestamp with time zone) <= '2019-10-31 00:00:00+00'::timestamp with time zone))
48.          

CTE promised_date1

49. 0.000 0.000 ↓ 0.0

Unique (cost=314,121.78..325,004.89 rows=536,073 width=20) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Sort (cost=314,121.78..319,563.33 rows=2,176,623 width=20) (actual rows= loops=)

  • Sort Key: events.resource_id, events.created_at
51. 0.000 0.000 ↓ 0.0

Nested Loop (cost=123.82..84,992.37 rows=2,176,623 width=20) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

HashAggregate (cost=123.25..125.25 rows=200 width=4) (actual rows= loops=)

  • Group Key: set_shipments.shipment_id
53. 0.000 0.000 ↓ 0.0

CTE Scan on set_shipments (cost=0.00..109.56 rows=5,478 width=4) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Index Scan using index_events_on_resource_id on events (cost=0.56..206.59 rows=8 width=277) (actual rows= loops=)

  • Index Cond: (resource_id = set_shipments.shipment_id)
  • Filter: (((resource_type)::text = 'Shipment'::text) AND ((((content)::jsonb -> 'changes'::text) -> 'scheduled_date'::text) IS NOT NULL))
55.          

CTE promised_window1

56. 0.000 0.000 ↓ 0.0

Unique (cost=357,654.24..368,537.35 rows=536,073 width=28) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Sort (cost=357,654.24..363,095.79 rows=2,176,623 width=28) (actual rows= loops=)

  • Sort Key: events_1.resource_id, events_1.created_at
58. 0.000 0.000 ↓ 0.0

Nested Loop (cost=123.82..128,524.83 rows=2,176,623 width=28) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

HashAggregate (cost=123.25..125.25 rows=200 width=4) (actual rows= loops=)

  • Group Key: set_shipments_1.shipment_id
60. 0.000 0.000 ↓ 0.0

CTE Scan on set_shipments set_shipments_1 (cost=0.00..109.56 rows=5,478 width=4) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Index Scan using index_events_on_resource_id on events events_1 (cost=0.56..206.59 rows=8 width=277) (actual rows= loops=)

  • Index Cond: (resource_id = set_shipments_1.shipment_id)
  • Filter: (((resource_type)::text = 'Shipment'::text) AND ((((content)::jsonb -> 'changes'::text) -> 'scheduled_deliver_after'::text) IS NOT NULL))
62.          

CTE promised_date2

63. 0.000 0.000 ↓ 0.0

Unique (cost=50,441.68..50,880.27 rows=87,718 width=20) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Sort (cost=50,441.68..50,660.97 rows=87,718 width=20) (actual rows= loops=)

  • Sort Key: events_2.resource_id, events_2.created_at DESC
65. 0.000 0.000 ↓ 0.0

Nested Loop (cost=123.82..43,239.77 rows=87,718 width=20) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

HashAggregate (cost=123.25..125.25 rows=200 width=4) (actual rows= loops=)

  • Group Key: set_shipments_2.shipment_id
67. 0.000 0.000 ↓ 0.0

CTE Scan on set_shipments set_shipments_2 (cost=0.00..109.56 rows=5,478 width=4) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

Index Scan using index_events_on_resource_id on events events_2 (cost=0.56..206.79 rows=1 width=277) (actual rows= loops=)

  • Index Cond: (resource_id = set_shipments_2.shipment_id)
  • Filter: ((platform_id <> 1) AND ((resource_type)::text = 'Shipment'::text) AND ((((content)::jsonb -> 'changes'::text) -> 'scheduled_date'::text) IS NOT NULL))
69.          

CTE promised_window2

70. 0.000 0.000 ↓ 0.0

Unique (cost=52,196.04..52,634.63 rows=87,718 width=28) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Sort (cost=52,196.04..52,415.33 rows=87,718 width=28) (actual rows= loops=)

  • Sort Key: events_3.resource_id, events_3.created_at DESC
72. 0.000 0.000 ↓ 0.0

Nested Loop (cost=123.82..44,994.13 rows=87,718 width=28) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

HashAggregate (cost=123.25..125.25 rows=200 width=4) (actual rows= loops=)

  • Group Key: set_shipments_3.shipment_id
74. 0.000 0.000 ↓ 0.0

CTE Scan on set_shipments set_shipments_3 (cost=0.00..109.56 rows=5,478 width=4) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

Index Scan using index_events_on_resource_id on events events_3 (cost=0.56..206.79 rows=1 width=277) (actual rows= loops=)

  • Index Cond: (resource_id = set_shipments_3.shipment_id)
  • Filter: ((platform_id <> 1) AND ((resource_type)::text = 'Shipment'::text) AND ((((content)::jsonb -> 'changes'::text) -> 'scheduled_deliver_after'::text) IS NOT NULL))
76.          

CTE promised_date

77. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=70,690.62..3,597,873.06 rows=235,116,257 width=12) (actual rows= loops=)

  • Merge Cond: (promised_date2.resource_id = promised_date1.resource_id)
78. 0.000 0.000 ↓ 0.0

Sort (cost=8,956.26..9,175.56 rows=87,718 width=12) (actual rows= loops=)

  • Sort Key: promised_date2.resource_id
79. 0.000 0.000 ↓ 0.0

CTE Scan on promised_date2 (cost=0.00..1,754.36 rows=87,718 width=12) (actual rows= loops=)

80. 0.000 0.000 ↓ 0.0

Sort (cost=61,734.35..63,074.54 rows=536,073 width=12) (actual rows= loops=)

  • Sort Key: promised_date1.resource_id
81. 0.000 0.000 ↓ 0.0

CTE Scan on promised_date1 (cost=0.00..10,721.46 rows=536,073 width=12) (actual rows= loops=)

82.          

CTE promised_window

83. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=70,690.62..3,597,873.06 rows=235,116,257 width=20) (actual rows= loops=)

  • Merge Cond: (promised_window2.resource_id = promised_window1.resource_id)
84. 0.000 0.000 ↓ 0.0

Sort (cost=8,956.26..9,175.56 rows=87,718 width=20) (actual rows= loops=)

  • Sort Key: promised_window2.resource_id
85. 0.000 0.000 ↓ 0.0

CTE Scan on promised_window2 (cost=0.00..1,754.36 rows=87,718 width=20) (actual rows= loops=)

86. 0.000 0.000 ↓ 0.0

Sort (cost=61,734.35..63,074.54 rows=536,073 width=20) (actual rows= loops=)

  • Sort Key: promised_window1.resource_id
87. 0.000 0.000 ↓ 0.0

CTE Scan on promised_window1 (cost=0.00..10,721.46 rows=536,073 width=20) (actual rows= loops=)

88.          

CTE promises_kept

89. 0.000 0.000 ↓ 0.0

Merge Join (cost=83,627,931.47..586,717,702,915,018.38 rows=7,570,548,657,164,246 width=136) (actual rows= loops=)

  • Merge Cond: (w.resource_id = d.resource_id)
90. 0.000 0.000 ↓ 0.0

Sort (cost=42,215,551.37..42,803,342.01 rows=235,116,257 width=20) (actual rows= loops=)

  • Sort Key: w.resource_id
91. 0.000 0.000 ↓ 0.0

CTE Scan on promised_window w (cost=0.00..4,702,325.14 rows=235,116,257 width=20) (actual rows= loops=)

92. 0.000 0.000 ↓ 0.0

Materialize (cost=41,412,380.10..154,697,298.01 rows=6,439,834,279 width=96) (actual rows= loops=)

93. 0.000 0.000 ↓ 0.0

Merge Join (cost=41,412,380.10..138,597,712.31 rows=6,439,834,279 width=96) (actual rows= loops=)

  • Merge Cond: (s.shipment_id = d.resource_id)
94. 0.000 0.000 ↓ 0.0

Sort (cost=449.73..463.42 rows=5,478 width=84) (actual rows= loops=)

  • Sort Key: s.shipment_id
95. 0.000 0.000 ↓ 0.0

CTE Scan on set_shipments s (cost=0.00..109.56 rows=5,478 width=84) (actual rows= loops=)

96. 0.000 0.000 ↓ 0.0

Materialize (cost=41,411,930.37..42,587,511.65 rows=235,116,257 width=12) (actual rows= loops=)

97. 0.000 0.000 ↓ 0.0

Sort (cost=41,411,930.37..41,999,721.01 rows=235,116,257 width=12) (actual rows= loops=)

  • Sort Key: d.resource_id
98. 0.000 0.000 ↓ 0.0

CTE Scan on promised_date d (cost=0.00..4,702,325.14 rows=235,116,257 width=12) (actual rows= loops=)

99. 0.000 0.000 ↓ 0.0

CTE Scan on promises_kept (cost=0.00..227,116,459,714,927.38 rows=37,852,743,285,821 width=16) (actual rows= loops=)

  • Filter: ((date_trunc('week'::text, promised_date) >= '2019-08-31 00:00:00'::timestamp without time zone) AND (date_trunc('week'::text, promised_date) <= '2019-10-31 00:00:00'::timestamp without time zone))