explain.depesz.com

PostgreSQL's explain analyze made readable

Result: u8Ssy

Settings
# exclusive inclusive rows x rows loops node
1. 1.218 179,302.863 ↓ 551.2 12,677 1

Limit (cost=197,343,141.12..197,346,294.78 rows=23 width=1,637) (actual time=177,728.928..179,302.863 rows=12,677 loops=1)

2.          

CTE schedule_block_report

3. 0.305 7,322.021 ↑ 1.4 1,446 1

Nested Loop (cost=111,926.39..175,478.77 rows=1,991 width=151) (actual time=717.920..7,322.021 rows=1,446 loops=1)

4. 0.026 0.026 ↑ 1.0 1 1

Seq Scan on tenant (cost=0.00..2.26 rows=1 width=8) (actual time=0.013..0.026 rows=1 loops=1)

  • Filter: ((NOT retired) AND (tenant_id = 511))
  • Rows Removed by Filter: 7
5. 2.561 7,321.690 ↑ 1.4 1,446 1

Hash Join (cost=111,926.39..175,416.78 rows=1,991 width=151) (actual time=717.905..7,321.690 rows=1,446 loops=1)

  • Hash Cond: (schedule_block.schedule_id = schedule.schedule_id)
6. 6,761.360 7,268.448 ↑ 1.2 9,388 1

Bitmap Heap Scan on schedule_block (cost=3,636.50..67,024.29 rows=11,430 width=143) (actual time=517.383..7,268.448 rows=9,388 loops=1)

  • Recheck Cond: (tenant_id = 511)
  • Filter: ((NOT created_by_app) AND ('2019-01-01 00:00:00+00'::timestamp with time zone <= end_dt) AND ('2019-12-31 00:00:00+00'::timestamp with time zone >= start_dt) AND ((block_type)::text = 'DEFAULT'::text))
  • Rows Removed by Filter: 124806
  • Heap Blocks: exact=23695
7. 507.088 507.088 ↑ 1.0 134,194 1

Bitmap Index Scan on idx_fk_schedule_block_tenant_id (cost=0.00..3,633.64 rows=135,095 width=0) (actual time=507.088..507.088 rows=134,194 loops=1)

  • Index Cond: (tenant_id = 511)
8. 0.051 50.681 ↑ 47.4 88 1

Hash (cost=108,154.24..108,154.24 rows=4,174 width=16) (actual time=50.680..50.681 rows=88 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 69kB
9. 8.570 50.630 ↑ 47.4 88 1

Index Scan using idx_schedule_tenant_id on schedule (cost=0.29..108,154.24 rows=4,174 width=16) (actual time=14.740..50.630 rows=88 loops=1)

  • Index Cond: (tenant_id = 511)
  • Filter: (SubPlan 1)
  • Rows Removed by Filter: 8324
10.          

SubPlan (for Index Scan)

11. 1.424 42.060 ↓ 0.0 0 8,412

Nested Loop (cost=3.23..12.81 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=8,412)

12. 0.000 25.236 ↓ 0.0 0 8,412

Bitmap Heap Scan on mv_service_provider (cost=2.80..7.31 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=8,412)

  • Recheck Cond: (person_party_id = schedule.party_id)
  • Filter: ((tenant_id = 511) AND (service_party_id = 3169422))
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=2046
13. 25.236 25.236 ↓ 0.0 0 8,412

Bitmap Index Scan on mv_service_provider_person (cost=0.00..2.80 rows=2 width=0) (actual time=0.003..0.003 rows=0 loops=8,412)

  • Index Cond: (person_party_id = schedule.party_id)
14. 15.400 15.400 ↑ 1.0 1 88

Index Scan using idx_pp_id_sfstatus on person_party staff_user (cost=0.43..5.47 rows=1 width=8) (actual time=0.175..0.175 rows=1 loops=88)

  • Index Cond: ((id = schedule.party_id) AND (starfish_status = 0))
  • Filter: (tenant_id = 511)
15.          

CTE block_dates

16. 15.212 7,619.800 ↑ 30,409.6 15,998 1

Unique (cost=161,739,728.42..165,388,422.99 rows=486,492,610 width=16) (actual time=7,591.640..7,619.800 rows=15,998 loops=1)

17.          

CTE weekly_days

18. 7.807 7,337.057 ↑ 13,231.0 39,576 1

Nested Loop (cost=125.03..7,850,702.35 rows=523,630,000 width=16) (actual time=7,325.752..7,337.057 rows=39,576 loops=1)

19. 0.178 7,324.912 ↓ 144.6 1,446 1

Unique (cost=124.60..124.65 rows=10 width=8) (actual time=7,324.510..7,324.912 rows=1,446 loops=1)

20. 0.770 7,324.734 ↓ 144.6 1,446 1

Sort (cost=124.60..124.63 rows=10 width=8) (actual time=7,324.509..7,324.734 rows=1,446 loops=1)

  • Sort Key: schedule_block_report_1.temporal_expression_id
  • Sort Method: quicksort Memory: 116kB
21. 7,323.964 7,323.964 ↓ 144.6 1,446 1

CTE Scan on schedule_block_report schedule_block_report_1 (cost=0.00..124.44 rows=10 width=8) (actual time=717.928..7,323.964 rows=1,446 loops=1)

  • Filter: (tenant_id = 511)
22. 4.338 4.338 ↑ 1.0 1 1,446

Index Scan using pk_temporal_expression on temporal_expression temporal_expression_1 (cost=0.42..5.46 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=1,446)

  • Index Cond: (temporal_expression_id = schedule_block_report_1.temporal_expression_id)
23. 18.777 7,604.588 ↑ 30,409.6 15,998 1

Sort (cost=153,889,026.06..155,105,257.59 rows=486,492,610 width=16) (actual time=7,591.639..7,604.588 rows=15,998 loops=1)

  • Sort Key: weekly_days.possible_day, weekly_days.temporal_expression_id
  • Sort Method: quicksort Memory: 1134kB
24. 1.325 7,585.811 ↑ 30,409.6 15,998 1

Append (cost=2,424.41..57,563,407.12 rows=486,492,610 width=16) (actual time=7,536.171..7,585.811 rows=15,998 loops=1)

25. 21.679 7,577.742 ↑ 8.4 10,981 1

Hash Join (cost=2,424.41..34,454,198.38 rows=92,610 width=16) (actual time=7,536.170..7,577.742 rows=10,981 loops=1)

  • Hash Cond: (weekly_days.temporal_expression_id = temporal_expression_2.temporal_expression_id)
  • Join Filter: ((((pow('2'::double precision, date_part('isodow'::text, weekly_days.possible_day)))::integer & temporal_expression_2.weekly_days) <> 0) AND ((((date_part('week'::text, weekly_days.possible_day) - date_part('week'::text, temporal_expression_2.start_date)))::integer % temporal_expression_2.frequency) = 0))
  • Rows Removed by Join Filter: 18293
26. 7,346.000 7,346.000 ↑ 13,231.0 39,576 1

CTE Scan on weekly_days (cost=0.00..31,417,800.00 rows=523,630,000 width=16) (actual time=7,325.753..7,346.000 rows=39,576 loops=1)

27. 0.131 210.063 ↑ 187.1 199 1

Hash (cost=1,214.44..1,214.44 rows=37,230 width=32) (actual time=210.063..210.063 rows=199 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 526kB
28. 0.628 209.932 ↑ 187.1 199 1

Nested Loop (cost=124.86..1,214.44 rows=37,230 width=32) (actual time=3.087..209.932 rows=199 loops=1)

29. 0.956 1.080 ↓ 7.2 1,446 1

HashAggregate (cost=124.44..130.44 rows=200 width=8) (actual time=0.603..1.080 rows=1,446 loops=1)

  • Group Key: schedule_block_report_2.temporal_expression_id
30. 0.124 0.124 ↑ 1.4 1,446 1

CTE Scan on schedule_block_report schedule_block_report_2 (cost=0.00..119.46 rows=1,991 width=8) (actual time=0.001..0.124 rows=1,446 loops=1)

31. 208.224 208.224 ↓ 0.0 0 1,446

Index Scan using pk_temporal_expression on temporal_expression temporal_expression_2 (cost=0.42..5.39 rows=1 width=24) (actual time=0.144..0.144 rows=0 loops=1,446)

  • Index Cond: (temporal_expression_id = schedule_block_report_2.temporal_expression_id)
  • Filter: ((te_type)::text = 'WEEKLY'::text)
  • Rows Removed by Filter: 1
32. 0.703 6.744 ↑ 96,950.4 5,017 1

Subquery Scan on *SELECT* 2 (cost=124.86..23,106,430.44 rows=486,400,000 width=16) (actual time=0.563..6.744 rows=5,017 loops=1)

33. 2.362 6.041 ↑ 96,950.4 5,017 1

Nested Loop (cost=124.86..7,298,430.44 rows=486,400,000 width=12) (actual time=0.560..6.041 rows=5,017 loops=1)

34. 0.651 0.787 ↓ 7.2 1,446 1

HashAggregate (cost=124.44..130.44 rows=200 width=8) (actual time=0.537..0.787 rows=1,446 loops=1)

  • Group Key: schedule_block_report_3.temporal_expression_id
35. 0.136 0.136 ↑ 1.4 1,446 1

CTE Scan on schedule_block_report schedule_block_report_3 (cost=0.00..119.46 rows=1,991 width=8) (actual time=0.002..0.136 rows=1,446 loops=1)

36. 2.892 2.892 ↑ 1.0 1 1,446

Index Scan using pk_temporal_expression on temporal_expression temporal_expression_3 (cost=0.42..5.39 rows=1 width=29) (actual time=0.002..0.002 rows=1 loops=1,446)

  • Index Cond: (temporal_expression_id = schedule_block_report_3.temporal_expression_id)
  • Filter: ((te_type)::text = 'DAILY'::text)
  • Rows Removed by Filter: 0
37. 48.467 179,301.645 ↓ 551.2 12,677 1

Result (cost=31,779,239.35..31,782,393.02 rows=23 width=1,637) (actual time=177,728.925..179,301.645 rows=12,677 loops=1)

38. 64.735 177,719.261 ↓ 551.2 12,677 1

Sort (cost=31,779,239.35..31,779,239.41 rows=23 width=525) (actual time=177,714.827..177,719.261 rows=12,677 loops=1)

  • Sort Key: staff_member.cms_user_ext_id, block_dates.block_date, temporal_expression.start_hour, temporal_expression.start_minute, temporal_expression.end_hour, temporal_expression.end_minute
  • Sort Method: quicksort Memory: 3700kB
39. 78,492.429 177,654.526 ↓ 551.2 12,677 1

Nested Loop Left Join (cost=125.61..31,779,238.83 rows=23 width=525) (actual time=7,624.814..177,654.526 rows=12,677 loops=1)

  • Join Filter: ((excluded_ranges.temporal_expression_id = schedule_block_report.temporal_expression_id) AND (block_dates.block_date = date(excluded_ranges.start_date)))
  • Rows Removed by Join Filter: 1054067165
40. 23.641 7,976.436 ↓ 551.2 12,677 1

Nested Loop (cost=125.61..31,699,465.60 rows=23 width=544) (actual time=7,592.541..7,976.436 rows=12,677 loops=1)

41. 46.088 7,851.379 ↓ 551.2 12,677 1

Nested Loop (cost=125.19..31,699,339.22 rows=23 width=470) (actual time=7,592.532..7,851.379 rows=12,677 loops=1)

  • Join Filter: (schedule_block_report.temporal_expression_id = temporal_expression.temporal_expression_id)
42. 41.897 7,691.198 ↑ 9.6 12,677 1

Hash Join (cost=124.76..31,634,914.84 rows=121,623 width=462) (actual time=7,592.517..7,691.198 rows=12,677 loops=1)

  • Hash Cond: (block_dates.temporal_expression_id = schedule_block_report.temporal_expression_id)
43. 7,648.586 7,648.586 ↑ 191.9 12,677 1

CTE Scan on block_dates (cost=0.00..31,622,019.65 rows=2,432,463 width=16) (actual time=7,591.786..7,648.586 rows=12,677 loops=1)

  • Filter: ((block_date >= '2019-01-01 00:00:00+00'::timestamp with time zone) AND (block_date < '2019-12-31 00:00:00+00'::timestamp with time zone))
  • Rows Removed by Filter: 3321
44. 0.310 0.715 ↓ 144.6 1,446 1

Hash (cost=124.44..124.44 rows=10 width=446) (actual time=0.715..0.715 rows=1,446 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 252kB
45. 0.405 0.405 ↓ 144.6 1,446 1

CTE Scan on schedule_block_report (cost=0.00..124.44 rows=10 width=446) (actual time=0.005..0.405 rows=1,446 loops=1)

  • Filter: (tenant_id = 511)
46. 114.093 114.093 ↑ 1.0 1 12,677

Index Scan using pk_temporal_expression on temporal_expression (cost=0.42..0.50 rows=1 width=24) (actual time=0.009..0.009 rows=1 loops=12,677)

  • Index Cond: (temporal_expression_id = block_dates.temporal_expression_id)
47. 101.416 101.416 ↑ 1.0 1 12,677

Index Scan using idx_pp_id_sfstatus on person_party staff_member (cost=0.43..5.47 rows=1 width=90) (actual time=0.006..0.008 rows=1 loops=12,677)

  • Index Cond: (id = schedule_block_report.staff_member_id)
48. 91,175.656 91,185.661 ↓ 1.0 83,148 12,677

Materialize (cost=0.00..3,496.69 rows=83,134 width=16) (actual time=0.003..7.193 rows=83,148 loops=12,677)

49. 10.005 10.005 ↓ 1.0 83,148 1

Seq Scan on excluded_ranges (cost=0.00..3,081.02 rows=83,134 width=16) (actual time=0.018..10.005 rows=83,148 loops=1)

50.          

SubPlan (for Result)

51. 12.677 481.726 ↑ 1.0 1 12,677

Aggregate (cost=18.00..18.03 rows=1 width=32) (actual time=0.038..0.038 rows=1 loops=12,677)

52. 13.146 469.049 ↑ 2.0 1 12,677

Nested Loop (cost=0.71..17.99 rows=2 width=10) (actual time=0.032..0.037 rows=1 loops=12,677)

53. 367.633 367.633 ↑ 2.0 1 12,677

Index Scan using janice_events on schedule_block_locations (cost=0.42..7.29 rows=2 width=8) (actual time=0.028..0.029 rows=1 loops=12,677)

  • Index Cond: (schedule_event_id = schedule_block_report.schedule_block_id)
54. 88.270 88.270 ↑ 1.0 1 12,610

Index Scan using pk_location on location (cost=0.29..5.32 rows=1 width=18) (actual time=0.007..0.007 rows=1 loops=12,610)

  • Index Cond: (location_id = schedule_block_locations.location_id)
55. 12.677 545.111 ↑ 1.0 1 12,677

Aggregate (cost=97.16..97.19 rows=1 width=32) (actual time=0.043..0.043 rows=1 loops=12,677)

56. 12.677 532.434 ↓ 0.0 0 12,677

Result (cost=91.97..97.14 rows=1 width=34) (actual time=0.042..0.042 rows=0 loops=12,677)

  • One-Time Filter: ((schedule_block_report.block_type)::text = 'DEFAULT'::text)
57. 0.000 519.757 ↓ 0.0 0 12,677

Nested Loop (cost=91.97..97.14 rows=1 width=34) (actual time=0.041..0.041 rows=0 loops=12,677)

58. 0.000 519.757 ↓ 0.0 0 12,677

Nested Loop (cost=91.55..96.63 rows=1 width=8) (actual time=0.038..0.041 rows=0 loops=12,677)

59. 12.677 431.018 ↓ 0.0 0 12,677

Unique (cost=91.12..91.13 rows=1 width=8) (actual time=0.034..0.034 rows=0 loops=12,677)

60. 12.677 418.341 ↓ 0.0 0 12,677

Sort (cost=91.12..91.13 rows=1 width=8) (actual time=0.033..0.033 rows=0 loops=12,677)

  • Sort Key: appointment.section_id
  • Sort Method: quicksort Memory: 25kB
61. 405.664 405.664 ↓ 0.0 0 12,677

Index Scan using idx_fk_appointment_schedule_block on appointment (cost=0.43..91.11 rows=1 width=8) (actual time=0.031..0.032 rows=0 loops=12,677)

  • Index Cond: (block_schedule_id = schedule_block_report.schedule_block_id)
  • Filter: ((cancel_dt IS NULL) AND (tenant_id = 511) AND (block_dates.block_date = date(start_dt)))
  • Rows Removed by Filter: 37
62. 94.256 94.256 ↓ 0.0 0 1,096

Index Scan using pk_section on section (cost=0.43..5.47 rows=1 width=16) (actual time=0.086..0.086 rows=0 loops=1,096)

  • Index Cond: (section_id = appointment.section_id)
  • Filter: (tenant_id = 511)
63. 0.000 0.000 ↓ 0.0 0 108

Index Scan using pk_course on course (cost=0.42..0.48 rows=1 width=42) (actual time=0.000..0.000 rows=0 loops=108)

  • Index Cond: (id = section.course_id)
64. 25.354 25.354 ↑ 1.0 1 12,677

Index Scan using idx_pp_id_sfstatus on person_party created_by_staff_member (cost=0.43..5.47 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=12,677)

  • Index Cond: (id = schedule_block_report.created_by)
65. 12.677 12.677 ↑ 1.0 1 12,677

Index Scan using idx_pp_id_sfstatus on person_party updated_by_staff_member (cost=0.43..5.47 rows=1 width=14) (actual time=0.001..0.001 rows=1 loops=12,677)

  • Index Cond: (id = schedule_block_report.updated_by)
66. 0.000 469.049 ↑ 1.0 1 12,677

Aggregate (cost=10.81..10.84 rows=1 width=32) (actual time=0.037..0.037 rows=1 loops=12,677)

67. 16.233 469.049 ↓ 2.0 2 12,677

Nested Loop (cost=0.70..10.80 rows=1 width=28) (actual time=0.034..0.037 rows=2 loops=12,677)

68. 431.018 431.018 ↓ 2.0 2 12,677

Index Only Scan using pk_schedule_block_appointment_types on schedule_block_appointment_types (cost=0.42..5.46 rows=1 width=8) (actual time=0.033..0.034 rows=2 loops=12,677)

  • Index Cond: (schedule_event_id = schedule_block_report.schedule_block_id)
  • Heap Fetches: 21798
69. 21.798 21.798 ↑ 1.0 1 21,798

Index Scan using pk_appointment_type_id on appointment_type (cost=0.28..5.31 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=21,798)

  • Index Cond: (appointment_type_id = schedule_block_appointment_types.appointment_type_id)
Planning time : 65.617 ms
Execution time : 179,308.108 ms