explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mVAQ

Settings
# exclusive inclusive rows x rows loops node
1. 23.399 214.917 ↓ 125.0 750 1

CTE Scan on pro_rata_calc (cost=28,392.81..28,436.39 rows=6 width=272) (actual time=212.353..214.917 rows=750 loops=1)

  • Filter: ((xdays >= ('20161017'::cstring)::date) AND (xdays <= ('20191011'::cstring)::date))
  • Rows Removed by Filter: 24
2.          

CTE dt_inis

3. 2.938 2.938 ↑ 1,000.0 1 1

Function Scan on fc_last_next_business_day (cost=0.25..10.25 rows=1,000 width=8) (actual time=2.938..2.938 rows=1 loops=1)

4.          

CTE dt_fins

5. 1.511 1.511 ↑ 1,000.0 1 1

Function Scan on fc_last_next_business_day fc_last_next_business_day_1 (cost=0.25..10.25 rows=1,000 width=8) (actual time=1.511..1.511 rows=1 loops=1)

6.          

CTE consts

7. 0.005 4.458 ↑ 1.0 1 1

Result (cost=80.00..80.01 rows=1 width=16) (actual time=4.458..4.458 rows=1 loops=1)

8.          

Initplan (for Result)

9. 2.939 2.939 ↑ 1,000.0 1 1

CTE Scan on dt_inis (cost=0.00..20.00 rows=1,000 width=4) (actual time=2.939..2.939 rows=1 loops=1)

10. 0.001 0.001 ↑ 1,000.0 1 1

CTE Scan on dt_inis dt_inis_1 (cost=0.00..20.00 rows=1,000 width=4) (actual time=0.000..0.001 rows=1 loops=1)

11. 1.512 1.512 ↑ 1,000.0 1 1

CTE Scan on dt_fins (cost=0.00..20.00 rows=1,000 width=4) (actual time=1.512..1.512 rows=1 loops=1)

12. 0.001 0.001 ↑ 1,000.0 1 1

CTE Scan on dt_fins dt_fins_1 (cost=0.00..20.00 rows=1,000 width=4) (actual time=0.000..0.001 rows=1 loops=1)

13.          

CTE calc_base

14. 0.000 10.056 ↓ 1.6 774 1

Sort (cost=171.71..172.94 rows=495 width=48) (actual time=10.011..10.056 rows=774 loops=1)

  • Sort Key: ((date_trunc('day'::text, dd.dd))::date)
  • Sort Method: quicksort Memory: 85kB
15.          

Initplan (for Sort)

16. 4.460 4.460 ↑ 1.0 1 1

CTE Scan on consts (cost=0.00..0.02 rows=1 width=8) (actual time=4.460..4.460 rows=1 loops=1)

17. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on consts consts_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1)

18. 0.924 9.791 ↓ 1.6 774 1

WindowAgg (cost=111.14..149.51 rows=495 width=48) (actual time=8.834..9.791 rows=774 loops=1)

19. 0.799 8.867 ↓ 1.6 774 1

Sort (cost=111.14..112.38 rows=495 width=44) (actual time=8.820..8.867 rows=774 loops=1)

  • Sort Key: ((lpad(((date_part('month'::text, ((date_trunc('day'::text, dd.dd))::date)::timestamp without time zone))::character varying)::text, 2, '0'::text) || ((date_part('year'::text, ((date_trunc('day'::text, dd.dd))::date)::timestamp without time zone))::character varying)::text)), ((date_trunc('day'::text, dd.dd))::date)
  • Sort Method: quicksort Memory: 85kB
20. 7.937 8.068 ↓ 1.6 774 1

Function Scan on generate_series dd (cost=26.69..88.99 rows=495 width=44) (actual time=5.021..8.068 rows=774 loops=1)

  • Filter: ((date_part('dow'::text, ((date_trunc('day'::text, dd))::date)::timestamp without time zone) <> ALL ('{6,0}'::double precision[])) AND (NOT (hashed SubPlan 8)))
  • Rows Removed by Filter: 353
21.          

SubPlan (for Function Scan)

22. 0.131 0.131 ↑ 1.0 935 1

Seq Scan on tb_holidays (cost=0.00..24.35 rows=935 width=4) (actual time=0.004..0.131 rows=935 loops=1)

23.          

CTE calc_list

24. 10.264 10.264 ↓ 1.6 774 1

CTE Scan on calc_base (cost=0.00..9.90 rows=495 width=48) (actual time=10.012..10.264 rows=774 loops=1)

25.          

CTE find_index_initial_dates

26. 0.005 0.341 ↑ 1.0 1 1

Result (cost=22.30..22.32 rows=1 width=4) (actual time=0.340..0.341 rows=1 loops=1)

27.          

Initplan (for Result)

28. 0.083 0.170 ↑ 1.0 1 1

Aggregate (cost=11.14..11.15 rows=1 width=4) (actual time=0.170..0.170 rows=1 loops=1)

29. 0.087 0.087 ↓ 1.6 774 1

CTE Scan on calc_list (cost=0.00..9.90 rows=495 width=4) (actual time=0.000..0.087 rows=774 loops=1)

30. 0.080 0.166 ↑ 1.0 1 1

Aggregate (cost=11.14..11.15 rows=1 width=4) (actual time=0.166..0.166 rows=1 loops=1)

31. 0.086 0.086 ↓ 1.6 774 1

CTE Scan on calc_list calc_list_1 (cost=0.00..9.90 rows=495 width=4) (actual time=0.000..0.086 rows=774 loops=1)

32.          

CTE add_index

33. 0.188 0.712 ↓ 39.0 39 1

Index Scan using tb_as_time_series_entry_pkey on tb_as_time_series_entry (cost=11.47..19.56 rows=1 width=53) (actual time=0.587..0.712 rows=39 loops=1)

  • Index Cond: ((id_time_series = 433) AND (dt_release >= $15) AND (dt_release <= $16))
  • Filter: (index_value IS NOT NULL)
34.          

Initplan (for Index Scan)

35. 0.346 0.346 ↑ 1.0 1 1

CTE Scan on find_index_initial_dates (cost=0.00..0.03 rows=1 width=4) (actual time=0.346..0.346 rows=1 loops=1)

36. 0.087 0.178 ↑ 1.0 1 1

Aggregate (cost=11.14..11.17 rows=1 width=4) (actual time=0.178..0.178 rows=1 loops=1)

37. 0.091 0.091 ↓ 1.6 774 1

CTE Scan on calc_list calc_list_2 (cost=0.00..9.90 rows=495 width=4) (actual time=0.000..0.091 rows=774 loops=1)

38.          

CTE add_all_indexes

39. 0.048 6.769 ↑ 12.9 39 1

HashAggregate (cost=81.78..86.80 rows=502 width=100) (actual time=6.754..6.769 rows=39 loops=1)

  • Group Key: add_index_1.last_index_value, add_index_1.dt_release, add_index_1.value, add_index_1.part
40. 0.006 6.721 ↑ 12.9 39 1

Append (cost=0.00..76.76 rows=502 width=100) (actual time=0.590..6.721 rows=39 loops=1)

41. 0.729 0.729 ↓ 39.0 39 1

CTE Scan on add_index add_index_1 (cost=0.00..0.02 rows=1 width=76) (actual time=0.589..0.729 rows=39 loops=1)

42. 5.983 5.985 ↓ 0.0 0 1

Function Scan on fc_calculate_anbima_ipca_ipgm_evolution_algorithm an (cost=0.39..69.14 rows=500 width=100) (actual time=5.985..5.985 rows=0 loops=1)

  • Filter: (NOT (hashed SubPlan 23))
43.          

Initplan (for Function Scan)

44. 0.000 0.000 ↓ 0.0 0

CTE Scan on consts consts_2 (cost=0.00..0.02 rows=1 width=4) (never executed)

45. 0.000 0.000 ↓ 0.0 0

CTE Scan on consts consts_3 (cost=0.00..0.02 rows=1 width=4) (never executed)

46. 0.000 0.000 ↓ 0.0 0

CTE Scan on consts consts_4 (cost=0.00..0.02 rows=1 width=4) (never executed)

47. 0.000 0.000 ↓ 0.0 0

CTE Scan on consts consts_5 (cost=0.00..0.02 rows=1 width=4) (never executed)

48. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on consts consts_6 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

49. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on consts consts_7 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

50.          

SubPlan (for Function Scan)

51. 0.000 0.000 ↓ 0.0 0

CTE Scan on add_index (cost=0.00..0.02 rows=1 width=32) (never executed)

52. 0.001 0.001 ↓ 0.0 0 1

Result (cost=0.08..0.08 rows=0 width=100) (actual time=0.001..0.001 rows=0 loops=1)

  • One-Time Filter: false
53.          

Initplan (for Result)

54. 0.000 0.000 ↓ 0.0 0

CTE Scan on consts consts_8 (cost=0.00..0.02 rows=1 width=4) (never executed)

55. 0.000 0.000 ↓ 0.0 0

CTE Scan on consts consts_9 (cost=0.00..0.02 rows=1 width=4) (never executed)

56. 0.000 0.000 ↓ 0.0 0

CTE Scan on consts consts_10 (cost=0.00..0.02 rows=1 width=4) (never executed)

57. 0.000 0.000 ↓ 0.0 0

CTE Scan on consts consts_11 (cost=0.00..0.02 rows=1 width=4) (never executed)

58.          

CTE calculation_plan

59. 2.146 199.891 ↑ 1.6 774 1

WindowAgg (cost=135.01..27,884.39 rows=1,242 width=208) (actual time=18.309..199.891 rows=774 loops=1)

60. 0.538 18.177 ↑ 1.6 774 1

Sort (cost=135.01..138.11 rows=1,242 width=112) (actual time=18.048..18.177 rows=774 loops=1)

  • Sort Key: calc_list_3.id_time_series, calc_list_3.xdays
  • Sort Method: quicksort Memory: 85kB
61. 0.216 17.639 ↑ 1.6 774 1

Hash Right Join (cost=16.09..71.18 rows=1,242 width=112) (actual time=17.412..17.639 rows=774 loops=1)

  • Hash Cond: (add_all_indexes.part = calc_list_3.part)
62. 6.787 6.787 ↑ 12.9 39 1

CTE Scan on add_all_indexes (cost=0.00..10.04 rows=502 width=96) (actual time=6.756..6.787 rows=39 loops=1)

63. 0.130 10.636 ↓ 1.6 774 1

Hash (cost=9.90..9.90 rows=495 width=48) (actual time=10.636..10.636 rows=774 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 51kB
64. 10.506 10.506 ↓ 1.6 774 1

CTE Scan on calc_list calc_list_3 (cost=0.00..9.90 rows=495 width=48) (actual time=10.014..10.506 rows=774 loops=1)

65.          

SubPlan (for WindowAgg)

66. 2.322 89.784 ↑ 1.0 1 774

Aggregate (cost=11.14..11.15 rows=1 width=8) (actual time=0.116..0.116 rows=1 loops=774)

67. 87.462 87.462 ↓ 10.5 21 774

CTE Scan on calc_base calc_base_1 (cost=0.00..11.14 rows=2 width=8) (actual time=0.055..0.113 rows=21 loops=774)

  • Filter: (part = calc_list_3.part)
  • Rows Removed by Filter: 753
68. 1.548 89.784 ↑ 1.0 1 774

Aggregate (cost=11.14..11.15 rows=1 width=8) (actual time=0.116..0.116 rows=1 loops=774)

69. 88.236 88.236 ↓ 10.5 21 774

CTE Scan on calc_base calc_base_2 (cost=0.00..11.14 rows=2 width=8) (actual time=0.055..0.114 rows=21 loops=774)

  • Filter: (part = calc_list_3.part)
  • Rows Removed by Filter: 753
70.          

CTE pro_rata_calc

71. 211.594 211.594 ↑ 1.6 774 1

CTE Scan on calculation_plan (cost=0.00..40.37 rows=1,242 width=240) (actual time=18.317..211.594 rows=774 loops=1)

72.          

CTE pro_rata_initial_index

73. 0.126 191.517 ↑ 6.0 1 1

CTE Scan on pro_rata_calc pro_rata_calc_2 (cost=27.96..55.90 rows=6 width=32) (actual time=191.394..191.517 rows=1 loops=1)

  • Filter: (xdays = $34)
  • Rows Removed by Filter: 773
74.          

Initplan (for CTE Scan)

75. 0.119 191.391 ↑ 1.0 1 1

Aggregate (cost=27.95..27.96 rows=1 width=4) (actual time=191.390..191.391 rows=1 loops=1)

76. 191.272 191.272 ↑ 1.6 774 1

CTE Scan on pro_rata_calc pro_rata_calc_1 (cost=0.00..24.84 rows=1,242 width=4) (actual time=0.000..191.272 rows=774 loops=1)

77.          

Initplan (for CTE Scan)

78. 191.518 191.518 ↑ 6.0 1 1

CTE Scan on pro_rata_initial_index (cost=0.00..0.12 rows=6 width=32) (actual time=191.395..191.518 rows=1 loops=1)

Planning time : 2.964 ms
Execution time : 217.246 ms