explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NSMp : Optimization for: Optimization for: Optimization for: Optimization for: plan #HMfn; plan #mhGX; plan #xkeD; plan #p3nk

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 430.151 430.151 ↑ 15.7 21 1

CTE Scan on dictation_summary ds (cost=4,099.64..4,108.76 rows=330 width=172) (actual time=6.349..430.151 rows=21 loops=1)

  • Filter: ((modality IS NOT NULL) OR ((modality IS NULL) AND (grouping_modality = 1)))
2.          

CTE cur_timestamp_table

3. 0.106 0.106 ↑ 1.0 1 1

Result (cost=0.00..0.26 rows=1 width=8) (actual time=0.106..0.106 rows=1 loops=1)

4.          

CTE cur_time

5. 0.003 0.119 ↑ 1.0 1 1

Nested Loop (cost=0.15..2.40 rows=1 width=12) (actual time=0.115..0.119 rows=1 loops=1)

6. 0.008 0.008 ↑ 1.0 1 1

Index Only Scan using idx__client__client_id on client (cost=0.15..2.37 rows=1 width=4) (actual time=0.006..0.008 rows=1 loops=1)

  • Index Cond: (client_id = 1335)
  • Heap Fetches: 1
7. 0.108 0.108 ↑ 1.0 1 1

CTE Scan on cur_timestamp_table (cost=0.00..0.02 rows=1 width=8) (actual time=0.107..0.108 rows=1 loops=1)

8.          

CTE curr_workshift

9. 0.020 0.152 ↑ 1.0 1 1

Nested Loop (cost=0.15..2.97 rows=1 width=116) (actual time=0.147..0.152 rows=1 loops=1)

  • Join Filter: (ws.client_id = ct.client_id)
10. 0.012 0.012 ↑ 1.0 1 1

Index Scan using workshift_pkey on workshift ws (cost=0.15..2.37 rows=1 width=68) (actual time=0.011..0.012 rows=1 loops=1)

  • Index Cond: (workshift_id = '1'::bigint)
11. 0.120 0.120 ↑ 1.0 1 1

CTE Scan on cur_time ct (cost=0.00..0.02 rows=1 width=12) (actual time=0.116..0.120 rows=1 loops=1)

12.          

CTE historical_workschedules

13. 0.022 0.120 ↓ 14.0 14 1

Sort (cost=7.51..7.52 rows=1 width=104) (actual time=0.108..0.120 rows=14 loops=1)

  • Sort Key: historical_workschedules.date DESC
  • Sort Method: quicksort Memory: 26kB
14.          

CTE cur_time

15. 0.004 0.004 ↑ 1.0 1 1

Result (cost=0.00..0.26 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1)

16.          

CTE curr_workshift

17. 0.008 0.017 ↑ 1.0 1 1

Nested Loop (cost=0.15..2.41 rows=1 width=24) (actual time=0.016..0.017 rows=1 loops=1)

18. 0.004 0.004 ↑ 1.0 1 1

Index Scan using workshift_pkey on workshift ws_1 (cost=0.15..2.37 rows=1 width=48) (actual time=0.004..0.004 rows=1 loops=1)

  • Index Cond: (workshift_id = '1'::bigint)
  • Filter: (client_id = 1335)
19. 0.005 0.005 ↑ 1.0 1 1

CTE Scan on cur_time ct_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=1)

20.          

CTE historical_workschedules

21. 0.024 0.090 ↓ 14.0 14 1

Nested Loop (cost=0.29..4.81 rows=1 width=104) (actual time=0.039..0.090 rows=14 loops=1)

  • Join Filter: (cws.worktask_id = ws_2.worktask_id)
22. 0.026 0.052 ↓ 14.0 14 1

Nested Loop (cost=0.14..2.41 rows=1 width=56) (actual time=0.030..0.052 rows=14 loops=1)

  • Join Filter: (((wsch.date)::date < (cws."current_time")::date) AND ((wsch.date)::date >= ((cws."current_time")::date - '28 days'::interval)))
  • Rows Removed by Join Filter: 2
23. 0.010 0.010 ↓ 16.0 16 1

Index Scan using idx__workschedule__client_id__workshift_id__dow on workschedule wsch (cost=0.14..2.36 rows=1 width=40) (actual time=0.007..0.010 rows=16 loops=1)

  • Index Cond: (client_id = 1335)
24. 0.016 0.016 ↑ 1.0 1 16

CTE Scan on curr_workshift cws (cost=0.00..0.02 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=16)

25. 0.014 0.014 ↑ 1.0 1 14

Index Scan using workshift_pkey on workshift ws_2 (cost=0.15..2.37 rows=1 width=64) (actual time=0.001..0.001 rows=1 loops=14)

  • Index Cond: (workshift_id = wsch.workshift_id)
  • Filter: (client_id = 1335)
26. 0.098 0.098 ↓ 14.0 14 1

CTE Scan on historical_workschedules (cost=0.00..0.02 rows=1 width=104) (actual time=0.040..0.098 rows=14 loops=1)

27.          

CTE historical_dictations

28. 9.837 419.801 ↓ 22.5 1,169 1

Nested Loop (cost=0.00..3,357.70 rows=52 width=48) (actual time=27.460..419.801 rows=1,169 loops=1)

  • Join Filter: (dictation.workschedule_id = hwsch.workschedule_id)
  • Rows Removed by Join Filter: 70889
29. 0.142 0.142 ↓ 14.0 14 1

CTE Scan on historical_workschedules hwsch (cost=0.00..0.02 rows=1 width=16) (actual time=0.108..0.142 rows=14 loops=1)

30. 409.822 409.822 ↓ 2.7 5,147 14

Seq Scan on dictation (cost=0.00..3,313.90 rows=1,934 width=472) (actual time=0.007..29.273 rows=5,147 loops=14)

  • Filter: ((NOT is_addendum) AND (client_id = 1335) AND (completed_time <= get_current_time()))
  • Rows Removed by Filter: 796
31.          

CTE todays_dictations

32. 0.238 4.722 ↓ 7.0 616 1

Nested Loop (cost=10.81..706.33 rows=88 width=47) (actual time=0.216..4.722 rows=616 loops=1)

  • Join Filter: ((CASE WHEN (dictation_1.completed_time <= get_current_time()) THEN dictation_1.completed_time ELSE NULL::timestamp with time zone END) <= cws_1."current_time")
33. 0.155 0.155 ↑ 1.0 1 1

CTE Scan on curr_workshift cws_1 (cost=0.00..0.03 rows=1 width=12) (actual time=0.150..0.155 rows=1 loops=1)

  • Filter: ((client_id = 1335) AND (workshift_id = '1'::bigint))
34. 4.290 4.329 ↓ 2.3 616 1

Bitmap Heap Scan on dictation dictation_1 (cost=10.81..700.17 rows=263 width=472) (actual time=0.061..4.329 rows=616 loops=1)

  • Recheck Cond: (workshift_id = '1'::bigint)
  • Filter: ((NOT is_addendum) AND (client_id = 1335) AND (completed_time <= get_current_time()))
  • Rows Removed by Filter: 192
  • Heap Blocks: exact=62
35. 0.039 0.039 ↑ 1.0 808 1

Bitmap Index Scan on idx__dictation__workshift_id (cost=0.00..10.74 rows=808 width=0) (actual time=0.039..0.039 rows=808 loops=1)

  • Index Cond: (workshift_id = '1'::bigint)
36.          

CTE dictation_summary

37. 0.006 430.123 ↑ 15.8 21 1

Append (cost=3.30..22.46 rows=332 width=104) (actual time=6.342..430.123 rows=21 loops=1)

38. 0.997 6.347 ↑ 25.1 7 1

HashAggregate (cost=3.30..8.58 rows=176 width=104) (actual time=6.342..6.347 rows=7 loops=1)

  • Hash Key: t.client_id, t.modality
  • Hash Key: t.client_id
39. 5.350 5.350 ↓ 7.0 616 1

CTE Scan on todays_dictations t (cost=0.00..1.76 rows=88 width=144) (actual time=0.309..5.350 rows=616 loops=1)

40. 1.983 422.808 ↑ 14.9 7 1

HashAggregate (cost=1.95..5.59 rows=104 width=104) (actual time=422.804..422.808 rows=7 loops=1)

  • Hash Key: h.client_id, h.modality
  • Hash Key: h.client_id
41. 420.825 420.825 ↓ 22.5 1,169 1

CTE Scan on historical_dictations h (cost=0.00..1.04 rows=52 width=144) (actual time=27.474..420.825 rows=1,169 loops=1)

42. 0.626 0.962 ↑ 7.4 7 1

HashAggregate (cost=1.50..3.32 rows=52 width=104) (actual time=0.958..0.962 rows=7 loops=1)

  • Hash Key: h_1.client_id, h_1.modality
  • Hash Key: h_1.client_id
43. 0.336 0.336 ↓ 24.8 645 1

CTE Scan on historical_dictations h_1 (cost=0.00..1.04 rows=26 width=144) (actual time=0.008..0.336 rows=645 loops=1)

  • Filter: precedes_current_time
  • Rows Removed by Filter: 524
Planning time : 3.432 ms
Execution time : 430.416 ms