explain.depesz.com

PostgreSQL's explain analyze made readable

Result: b0Pn

Settings
# exclusive inclusive rows x rows loops node
1. 231,303.121 231,303.479 ↓ 17.7 53 1

Merge Left Join (cost=2,181,582.29..2,181,932.27 rows=3 width=128) (actual time=231,303.317..231,303.479 rows=53 loops=1)

  • Merge Cond: (ts.mmwr_start = i.period_start)
2.          

CTE mmwr_calendar

3. 0.408 3.941 ↓ 1.0 1,044 1

Sort (cost=142.41..144.91 rows=1,000 width=32) (actual time=3.823..3.941 rows=1,044 loops=1)

  • Sort Key: a.period_start
  • Sort Method: quicksort Memory: 130kB
4. 1.185 3.533 ↓ 1.0 1,044 1

WindowAgg (cost=65.08..92.58 rows=1,000 width=32) (actual time=2.238..3.533 rows=1,044 loops=1)

5. 0.356 2.348 ↓ 1.0 1,044 1

Sort (cost=65.08..67.58 rows=1,000 width=16) (actual time=2.229..2.348 rows=1,044 loops=1)

  • Sort Key: (date_part('year'::text, (a.period_start + '3 days'::interval)))
  • Sort Method: quicksort Memory: 97kB
6. 1.992 1.992 ↓ 1.0 1,044 1

Function Scan on _date_aggregate a (cost=0.26..15.26 rows=1,000 width=16) (actual time=1.029..1.992 rows=1,044 loops=1)

7.          

CTE time_series

8. 5.142 5.142 ↓ 17.7 53 1

CTE Scan on mmwr_calendar (cost=0.00..37.50 rows=3 width=32) (actual time=4.730..5.142 rows=53 loops=1)

  • Filter: (((mmwr_year = (date_part('year'::text, '2020-10-10 00:00:00-04'::timestamp with time zone) - '1'::double precision)) AND (mmwr_number >= 40)) OR ((mmwr_year = date_part('year'::text, '2020-10-10 00:00:00-04'::timestamp with time zone)) AND (mmwr_number <= 40)))
  • Rows Removed by Filter: 991
9.          

CTE classification_arr

10. 0.042 0.317 ↑ 333.3 3 1

ProjectSet (cost=78.40..83.43 rows=1,000 width=36) (actual time=0.315..0.317 rows=3 loops=1)

11.          

Initplan (for ProjectSet)

12. 0.073 0.190 ↑ 1.0 1 1

Aggregate (cost=39.19..39.20 rows=1 width=32) (actual time=0.190..0.190 rows=1 loops=1)

13. 0.012 0.117 ↑ 1.0 3 1

Nested Loop (cost=0.42..39.18 rows=3 width=57) (actual time=0.088..0.117 rows=3 loops=1)

14. 0.081 0.081 ↑ 1.0 3 1

Index Scan using classification_pkey on classification c (cost=0.28..18.56 rows=3 width=34) (actual time=0.063..0.081 rows=3 loops=1)

  • Index Cond: (id = ANY ('{830,9,95}'::bigint[]))
15. 0.024 0.024 ↑ 1.0 1 3

Index Scan using classifier_pkey on classifier r (cost=0.14..6.83 rows=1 width=39) (actual time=0.008..0.008 rows=1 loops=3)

  • Index Cond: (id = c.id_classifier)
16. 0.026 0.084 ↑ 1.0 1 1

Aggregate (cost=39.19..39.20 rows=1 width=32) (actual time=0.084..0.084 rows=1 loops=1)

17. 0.011 0.058 ↑ 1.0 3 1

Nested Loop (cost=0.42..39.18 rows=3 width=57) (actual time=0.038..0.058 rows=3 loops=1)

18. 0.038 0.038 ↑ 1.0 3 1

Index Scan using classification_pkey on classification c_1 (cost=0.28..18.56 rows=3 width=34) (actual time=0.026..0.038 rows=3 loops=1)

  • Index Cond: (id = ANY ('{830,9,95}'::bigint[]))
19. 0.009 0.009 ↑ 1.0 1 3

Index Scan using classifier_pkey on classifier r_1 (cost=0.14..6.83 rows=1 width=39) (actual time=0.003..0.003 rows=1 loops=3)

  • Index Cond: (id = c_1.id_classifier)
20. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

21.          

Initplan (for Merge Left Join)

22. 0.328 0.328 ↑ 5.0 1 1

CTE Scan on classification_arr (cost=0.00..22.50 rows=5 width=32) (actual time=0.324..0.328 rows=1 loops=1)

  • Filter: (sort_order = 1)
  • Rows Removed by Filter: 2
23. 0.004 0.004 ↑ 5.0 1 1

CTE Scan on classification_arr classification_arr_1 (cost=0.00..22.50 rows=5 width=32) (actual time=0.003..0.004 rows=1 loops=1)

  • Filter: (sort_order = 1)
  • Rows Removed by Filter: 2
24. 0.003 0.003 ↑ 5.0 1 1

CTE Scan on classification_arr classification_arr_2 (cost=0.00..22.50 rows=5 width=32) (actual time=0.002..0.003 rows=1 loops=1)

  • Filter: (sort_order = 2)
  • Rows Removed by Filter: 2
25. 0.002 0.002 ↑ 5.0 1 1

CTE Scan on classification_arr classification_arr_3 (cost=0.00..22.50 rows=5 width=32) (actual time=0.002..0.002 rows=1 loops=1)

  • Filter: (sort_order = 2)
  • Rows Removed by Filter: 2
26. 0.002 0.002 ↑ 5.0 1 1

CTE Scan on classification_arr classification_arr_4 (cost=0.00..22.50 rows=5 width=32) (actual time=0.002..0.002 rows=1 loops=1)

  • Filter: (sort_order = 3)
  • Rows Removed by Filter: 2
27. 0.003 0.003 ↑ 5.0 1 1

CTE Scan on classification_arr classification_arr_5 (cost=0.00..22.50 rows=5 width=32) (actual time=0.003..0.003 rows=1 loops=1)

  • Filter: (sort_order = 3)
  • Rows Removed by Filter: 2
28. 0.002 0.002 ↓ 0.0 0 1

CTE Scan on classification_arr classification_arr_6 (cost=0.00..22.50 rows=5 width=32) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: (sort_order = 4)
  • Rows Removed by Filter: 3
29. 0.000 0.000 ↓ 0.0 0

CTE Scan on classification_arr classification_arr_7 (cost=0.00..22.50 rows=5 width=32) (never executed)

  • Filter: (sort_order = 4)
30. 0.003 0.003 ↓ 0.0 0 1

CTE Scan on classification_arr classification_arr_8 (cost=0.00..22.50 rows=5 width=32) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: (sort_order = 5)
  • Rows Removed by Filter: 3
31. 0.000 0.000 ↓ 0.0 0

CTE Scan on classification_arr classification_arr_9 (cost=0.00..22.50 rows=5 width=32) (never executed)

  • Filter: (sort_order = 5)
32. 0.002 0.002 ↓ 0.0 0 1

CTE Scan on classification_arr classification_arr_10 (cost=0.00..22.50 rows=5 width=32) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: (sort_order = 6)
  • Rows Removed by Filter: 3
33. 0.000 0.000 ↓ 0.0 0

CTE Scan on classification_arr classification_arr_11 (cost=0.00..22.50 rows=5 width=32) (never executed)

  • Filter: (sort_order = 6)
34. 0.002 0.002 ↓ 0.0 0 1

CTE Scan on classification_arr classification_arr_12 (cost=0.00..22.50 rows=5 width=32) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: (sort_order = 7)
  • Rows Removed by Filter: 3
35. 0.000 0.000 ↓ 0.0 0

CTE Scan on classification_arr classification_arr_13 (cost=0.00..22.50 rows=5 width=32) (never executed)

  • Filter: (sort_order = 7)
36. 0.002 0.002 ↓ 0.0 0 1

CTE Scan on classification_arr classification_arr_14 (cost=0.00..22.50 rows=5 width=32) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: (sort_order = 8)
  • Rows Removed by Filter: 3
37. 0.000 0.000 ↓ 0.0 0

CTE Scan on classification_arr classification_arr_15 (cost=0.00..22.50 rows=5 width=32) (never executed)

  • Filter: (sort_order = 8)
38. 0.003 0.003 ↓ 0.0 0 1

CTE Scan on classification_arr classification_arr_16 (cost=0.00..22.50 rows=5 width=32) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: (sort_order = 9)
  • Rows Removed by Filter: 3
39. 0.000 0.000 ↓ 0.0 0

CTE Scan on classification_arr classification_arr_17 (cost=0.00..22.50 rows=5 width=32) (never executed)

  • Filter: (sort_order = 9)
40. 0.002 0.002 ↓ 0.0 0 1

CTE Scan on classification_arr classification_arr_18 (cost=0.00..22.50 rows=5 width=32) (actual time=0.002..0.002 rows=0 loops=1)