explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3ioV

Settings
# exclusive inclusive rows x rows loops node
1. 53.316 53.316 ↑ 5.8 644 1

CTE Scan on uniont (cost=4,131.28..4,215.54 rows=3,726 width=136) (actual time=48.925..53.316 rows=644 loops=1)

  • Filter: (COALESCE(change, '0'::numeric) <> '0'::numeric)
  • Rows Removed by Filter: 1029
2.          

CTE plans

3. 2.920 8.358 ↑ 1.3 192 1

HashAggregate (cost=614.27..618.03 rows=251 width=72) (actual time=8.285..8.358 rows=192 loops=1)

  • Group Key: budget_spending_plan.budget_spending_kbk_id
4. 0.879 5.438 ↓ 2.8 3,523 1

Hash Join (cost=118.04..585.53 rows=1,277 width=24) (actual time=1.789..5.438 rows=3,523 loops=1)

  • Hash Cond: (budget_spending_plan.budget_spending_internal_kbk_id = budget_spending_internal_kbk.id)
5. 0.839 3.694 ↓ 2.8 3,523 1

Hash Join (cost=11.73..461.91 rows=1,277 width=28) (actual time=0.890..3.694 rows=3,523 loops=1)

  • Hash Cond: (budget_spending_plan.budget_spending_kbk_id = budget_spending_kbk.id)
6. 2.750 2.750 ↓ 2.8 3,523 1

Seq Scan on budget_spending_plan (cost=0.00..432.62 rows=1,277 width=28) (actual time=0.756..2.750 rows=3,523 loops=1)

  • Filter: ((month IS NOT NULL) AND (date_at >= '2019-01-01'::date) AND (date_at <= '2019-12-31'::date) AND (version_start_at < '2020-01-01 00:00:00'::timestamp without time zone) AND (month <= 12) AND (year = '2019'::smallint) AND (version_end_at = '2099-12-31 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 8342
7. 0.051 0.105 ↓ 1.1 316 1

Hash (cost=7.99..7.99 rows=299 width=4) (actual time=0.105..0.105 rows=316 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
8. 0.054 0.054 ↓ 1.1 316 1

Seq Scan on budget_spending_kbk (cost=0.00..7.99 rows=299 width=4) (actual time=0.008..0.054 rows=316 loops=1)

9. 0.432 0.865 ↑ 1.0 2,797 1

Hash (cost=71.14..71.14 rows=2,814 width=4) (actual time=0.865..0.865 rows=2,797 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 131kB
10. 0.433 0.433 ↑ 1.0 2,797 1

Seq Scan on budget_spending_internal_kbk (cost=0.00..71.14 rows=2,814 width=4) (actual time=0.009..0.433 rows=2,797 loops=1)

11.          

CTE facts

12. 2.569 7.232 ↑ 1.3 158 1

HashAggregate (cost=452.06..455.24 rows=212 width=72) (actual time=7.175..7.232 rows=158 loops=1)

  • Group Key: budget_spending_fact.budget_spending_kbk_id
13. 0.897 4.663 ↑ 1.0 3,300 1

Hash Join (cost=118.04..394.24 rows=3,304 width=21) (actual time=1.510..4.663 rows=3,300 loops=1)

  • Hash Cond: (budget_spending_fact.budget_spending_internal_kbk_id = budget_spending_internal_kbk_1.id)
14. 0.910 2.832 ↑ 1.0 3,300 1

Hash Join (cost=11.73..242.50 rows=3,304 width=25) (actual time=0.541..2.832 rows=3,300 loops=1)

  • Hash Cond: (budget_spending_fact.budget_spending_kbk_id = budget_spending_kbk_1.id)
15. 1.803 1.803 ↑ 1.0 3,300 1

Seq Scan on budget_spending_fact (cost=0.00..185.34 rows=3,304 width=25) (actual time=0.393..1.803 rows=3,300 loops=1)

  • Filter: ((date_at >= '2019-01-01'::date) AND (date_at <= '2019-12-31'::date) AND (version_start_at < '2020-01-01 00:00:00'::timestamp without time zone) AND (version_end_at = '2099-12-31 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 2267
16. 0.065 0.119 ↓ 1.1 316 1

Hash (cost=7.99..7.99 rows=299 width=4) (actual time=0.119..0.119 rows=316 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
17. 0.054 0.054 ↓ 1.1 316 1

Seq Scan on budget_spending_kbk budget_spending_kbk_1 (cost=0.00..7.99 rows=299 width=4) (actual time=0.009..0.054 rows=316 loops=1)

18. 0.473 0.934 ↑ 1.0 2,797 1

Hash (cost=71.14..71.14 rows=2,814 width=4) (actual time=0.934..0.934 rows=2,797 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 131kB
19. 0.461 0.461 ↑ 1.0 2,797 1

Seq Scan on budget_spending_internal_kbk budget_spending_internal_kbk_1 (cost=0.00..71.14 rows=2,814 width=4) (actual time=0.007..0.461 rows=2,797 loops=1)

20.          

CTE primary_year_plans

21. 0.309 6.275 ↑ 1.4 147 1

GroupAggregate (cost=886.98..895.10 rows=203 width=72) (actual time=5.987..6.275 rows=147 loops=1)

  • Group Key: budget_spending_plan_1.budget_spending_kbk_id
22. 0.158 5.966 ↓ 1.6 322 1

Sort (cost=886.98..887.48 rows=203 width=24) (actual time=5.943..5.966 rows=322 loops=1)

  • Sort Key: budget_spending_plan_1.budget_spending_kbk_id
  • Sort Method: quicksort Memory: 50kB
23. 0.063 5.808 ↓ 1.6 322 1

Nested Loop (cost=427.74..879.20 rows=203 width=24) (actual time=3.968..5.808 rows=322 loops=1)

24. 0.118 5.101 ↓ 1.6 322 1

Hash Join (cost=427.46..774.66 rows=203 width=28) (actual time=3.945..5.101 rows=322 loops=1)

  • Hash Cond: (budget_spending_plan_1.budget_spending_kbk_id = budget_spending_kbk_2.id)
25. 0.553 4.867 ↓ 1.6 322 1

Hash Join (cost=415.74..760.14 rows=203 width=28) (actual time=3.803..4.867 rows=322 loops=1)

  • Hash Cond: (budget_spending_document.id = budget_spending_plan_1.budget_spending_document_id)
26. 1.672 1.672 ↓ 1.1 4,740 1

Seq Scan on budget_spending_document (cost=0.00..326.65 rows=4,194 width=4) (actual time=0.009..1.672 rows=4,740 loops=1)

  • Filter: "primary
  • Rows Removed by Filter: 16512
27. 0.545 2.642 ↓ 2.0 1,993 1

Hash (cost=402.96..402.96 rows=1,022 width=32) (actual time=2.642..2.642 rows=1,993 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 149kB
28. 2.097 2.097 ↓ 2.0 1,993 1

Seq Scan on budget_spending_plan budget_spending_plan_1 (cost=0.00..402.96 rows=1,022 width=32) (actual time=0.687..2.097 rows=1,993 loops=1)

  • Filter: ((month IS NULL) AND (date_at >= '2019-01-01'::date) AND (date_at <= '2019-12-31'::date) AND (version_start_at < '2020-01-01 00:00:00'::timestamp without time zone) AND (year = '2019'::smallint) AND (version_end_at = '2099-12-31 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 9872
29. 0.062 0.116 ↓ 1.1 316 1

Hash (cost=7.99..7.99 rows=299 width=4) (actual time=0.116..0.116 rows=316 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
30. 0.054 0.054 ↓ 1.1 316 1

Seq Scan on budget_spending_kbk budget_spending_kbk_2 (cost=0.00..7.99 rows=299 width=4) (actual time=0.010..0.054 rows=316 loops=1)

31. 0.644 0.644 ↑ 1.0 1 322

Index Only Scan using budget_spending_internal_kbk_pkey on budget_spending_internal_kbk budget_spending_internal_kbk_2 (cost=0.28..0.50 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=322)

  • Index Cond: (id = budget_spending_plan_1.budget_spending_internal_kbk_id)
  • Heap Fetches: 322
32.          

CTE current_year_plans

33. 1.805 6.219 ↑ 1.3 192 1

HashAggregate (cost=571.90..575.62 rows=248 width=72) (actual time=6.151..6.219 rows=192 loops=1)

  • Group Key: budget_spending_plan_2.budget_spending_kbk_id
34. 0.593 4.414 ↓ 2.0 1,993 1

Hash Join (cost=118.04..548.91 rows=1,022 width=24) (actual time=1.847..4.414 rows=1,993 loops=1)

  • Hash Cond: (budget_spending_plan_2.budget_spending_internal_kbk_id = budget_spending_internal_kbk_3.id)
35. 0.550 2.939 ↓ 2.0 1,993 1

Hash Join (cost=11.73..428.74 rows=1,022 width=28) (actual time=0.930..2.939 rows=1,993 loops=1)

  • Hash Cond: (budget_spending_plan_2.budget_spending_kbk_id = budget_spending_kbk_3.id)
36. 2.264 2.264 ↓ 2.0 1,993 1

Seq Scan on budget_spending_plan budget_spending_plan_2 (cost=0.00..402.96 rows=1,022 width=28) (actual time=0.771..2.264 rows=1,993 loops=1)

  • Filter: ((month IS NULL) AND (date_at >= '2019-01-01'::date) AND (date_at <= '2019-12-31'::date) AND (version_start_at < '2020-01-01 00:00:00'::timestamp without time zone) AND (year = '2019'::smallint) AND (version_end_at = '2099-12-31 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 9872
37. 0.067 0.125 ↓ 1.1 316 1

Hash (cost=7.99..7.99 rows=299 width=4) (actual time=0.125..0.125 rows=316 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
38. 0.058 0.058 ↓ 1.1 316 1

Seq Scan on budget_spending_kbk budget_spending_kbk_3 (cost=0.00..7.99 rows=299 width=4) (actual time=0.007..0.058 rows=316 loops=1)

39. 0.455 0.882 ↑ 1.0 2,797 1

Hash (cost=71.14..71.14 rows=2,814 width=4) (actual time=0.882..0.882 rows=2,797 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 131kB
40. 0.427 0.427 ↑ 1.0 2,797 1

Seq Scan on budget_spending_internal_kbk budget_spending_internal_kbk_3 (cost=0.00..71.14 rows=2,814 width=4) (actual time=0.007..0.427 rows=2,797 loops=1)

41.          

CTE pf

42. 0.122 8.606 ↑ 1.4 193 1

Hash Full Join (cost=6.89..21.79 rows=266 width=140) (actual time=8.366..8.606 rows=193 loops=1)

  • Hash Cond: (plans.budget_spending_kbk_id = facts.budget_spending_kbk_id)
43. 8.436 8.436 ↑ 1.3 192 1

CTE Scan on plans (cost=0.00..5.02 rows=251 width=72) (actual time=8.288..8.436 rows=192 loops=1)

44. 0.030 0.048 ↑ 1.3 158 1

Hash (cost=4.24..4.24 rows=212 width=72) (actual time=0.048..0.048 rows=158 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
45. 0.018 0.018 ↑ 1.3 158 1

CTE Scan on facts (cost=0.00..4.24 rows=212 width=72) (actual time=0.002..0.018 rows=158 loops=1)

46.          

CTE budget_values

47. 0.201 13.854 ↑ 1.4 193 1

Hash Full Join (cost=6.89..22.93 rows=263 width=72) (actual time=13.541..13.854 rows=193 loops=1)

  • Hash Cond: (current_year_plans.budget_spending_kbk_id = facts_1.budget_spending_kbk_id)
48. 6.296 6.296 ↑ 1.3 192 1

CTE Scan on current_year_plans (cost=0.00..4.96 rows=248 width=72) (actual time=6.153..6.296 rows=192 loops=1)

49. 0.039 7.357 ↑ 1.3 158 1

Hash (cost=4.24..4.24 rows=212 width=72) (actual time=7.357..7.357 rows=158 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
50. 7.318 7.318 ↑ 1.3 158 1

CTE Scan on facts facts_1 (cost=0.00..4.24 rows=212 width=72) (actual time=7.177..7.318 rows=158 loops=1)

51.          

CTE month_plans

52. 1.065 5.927 ↑ 1.6 142 1

HashAggregate (cost=578.57..582.01 rows=229 width=72) (actual time=5.866..5.927 rows=142 loops=1)

  • Group Key: budget_spending_plan_3.budget_spending_kbk_id
53. 0.317 4.862 ↓ 1.7 1,029 1

Hash Join (cost=118.04..566.77 rows=590 width=22) (actual time=1.950..4.862 rows=1,029 loops=1)

  • Hash Cond: (budget_spending_plan_3.budget_spending_internal_kbk_id = budget_spending_internal_kbk_4.id)
54. 0.268 3.685 ↓ 1.7 1,029 1

Hash Join (cost=11.73..452.47 rows=590 width=26) (actual time=1.044..3.685 rows=1,029 loops=1)

  • Hash Cond: (budget_spending_plan_3.budget_spending_kbk_id = budget_spending_kbk_4.id)
55. 3.311 3.311 ↓ 1.7 1,029 1

Seq Scan on budget_spending_plan budget_spending_plan_3 (cost=0.00..432.62 rows=590 width=26) (actual time=0.905..3.311 rows=1,029 loops=1)

  • Filter: ((date_at >= '2019-01-01'::date) AND (date_at <= '2019-12-31'::date) AND (version_start_at < '2020-01-01 00:00:00'::timestamp without time zone) AND (year = '2019'::smallint) AND (version_end_at = '2099-12-31 00:00:00'::timestamp without time zone) AND (month = 12))
  • Rows Removed by Filter: 10836
56. 0.053 0.106 ↓ 1.1 316 1

Hash (cost=7.99..7.99 rows=299 width=4) (actual time=0.106..0.106 rows=316 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
57. 0.053 0.053 ↓ 1.1 316 1

Seq Scan on budget_spending_kbk budget_spending_kbk_4 (cost=0.00..7.99 rows=299 width=4) (actual time=0.007..0.053 rows=316 loops=1)

58. 0.433 0.860 ↑ 1.0 2,797 1

Hash (cost=71.14..71.14 rows=2,814 width=4) (actual time=0.860..0.860 rows=2,797 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 131kB
59. 0.427 0.427 ↑ 1.0 2,797 1

Seq Scan on budget_spending_internal_kbk budget_spending_internal_kbk_4 (cost=0.00..71.14 rows=2,814 width=4) (actual time=0.008..0.427 rows=2,797 loops=1)

60.          

CTE month_facts

61. 6.796 12.903 ↑ 1.0 212 1

HashAggregate (cost=665.24..669.48 rows=212 width=136) (actual time=12.802..12.903 rows=212 loops=1)

  • Group Key: budget_spending_fact_1.budget_spending_kbk_id
62. 1.838 6.107 ↑ 1.0 5,565 1

Hash Join (cost=118.04..428.64 rows=5,567 width=26) (actual time=1.066..6.107 rows=5,565 loops=1)

  • Hash Cond: (budget_spending_fact_1.budget_spending_internal_kbk_id = budget_spending_internal_kbk_5.id)
63. 1.498 3.397 ↑ 1.0 5,565 1

Hash Join (cost=11.73..245.78 rows=5,567 width=30) (actual time=0.151..3.397 rows=5,565 loops=1)

  • Hash Cond: (budget_spending_fact_1.budget_spending_kbk_id = budget_spending_kbk_5.id)
64. 1.789 1.789 ↑ 1.0 5,565 1

Seq Scan on budget_spending_fact budget_spending_fact_1 (cost=0.00..157.50 rows=5,567 width=30) (actual time=0.018..1.789 rows=5,565 loops=1)

  • Filter: ((version_start_at < '2020-01-01 00:00:00'::timestamp without time zone) AND (version_end_at = '2099-12-31 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 2
65. 0.062 0.110 ↓ 1.1 316 1

Hash (cost=7.99..7.99 rows=299 width=4) (actual time=0.110..0.110 rows=316 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
66. 0.048 0.048 ↓ 1.1 316 1

Seq Scan on budget_spending_kbk budget_spending_kbk_5 (cost=0.00..7.99 rows=299 width=4) (actual time=0.006..0.048 rows=316 loops=1)

67. 0.468 0.872 ↑ 1.0 2,797 1

Hash (cost=71.14..71.14 rows=2,814 width=4) (actual time=0.872..0.872 rows=2,797 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 131kB
68. 0.404 0.404 ↑ 1.0 2,797 1

Seq Scan on budget_spending_internal_kbk budget_spending_internal_kbk_5 (cost=0.00..71.14 rows=2,814 width=4) (actual time=0.007..0.404 rows=2,797 loops=1)

69.          

CTE opc

70. 0.507 19.593 ↑ 1.1 230 1

Hash Full Join (cost=6.89..27.77 rows=243 width=396) (actual time=18.994..19.593 rows=230 loops=1)

  • Hash Cond: (month_plans.budget_spending_kbk_id = month_facts.budget_spending_kbk_id)
71. 6.010 6.010 ↑ 1.6 142 1

CTE Scan on month_plans (cost=0.00..4.58 rows=229 width=72) (actual time=5.868..6.010 rows=142 loops=1)

72. 0.057 13.076 ↑ 1.0 212 1

Hash (cost=4.24..4.24 rows=212 width=136) (actual time=13.076..13.076 rows=212 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
73. 13.019 13.019 ↑ 1.0 212 1

CTE Scan on month_facts (cost=0.00..4.24 rows=212 width=136) (actual time=12.806..13.019 rows=212 loops=1)

74.          

CTE merget

75. 0.574 50.010 ↑ 2.2 239 1

Hash Full Join (cost=31.10..106.83 rows=535 width=820) (actual time=48.883..50.010 rows=239 loops=1)

  • Hash Cond: (COALESCE(pf.budget_spending_kbk_id, primary_year_plans.budget_spending_kbk_id, current_year_plans_1.budget_spending_kbk_id, opc.budget_spending_kbk_id) = budget_values.budget_spending_kbk_id)
76. 0.140 35.422 ↑ 1.7 239 1

Hash Full Join (cost=22.56..65.31 rows=407 width=584) (actual time=34.829..35.422 rows=239 loops=1)

  • Hash Cond: (COALESCE(pf.budget_spending_kbk_id, primary_year_plans.budget_spending_kbk_id, current_year_plans_1.budget_spending_kbk_id) = opc.budget_spending_kbk_id)
77. 0.113 15.379 ↑ 1.7 193 1

Hash Full Join (cost=14.66..42.46 rows=335 width=252) (actual time=14.896..15.379 rows=193 loops=1)

  • Hash Cond: (COALESCE(pf.budget_spending_kbk_id, primary_year_plans.budget_spending_kbk_id) = current_year_plans_1.budget_spending_kbk_id)
78. 0.105 15.183 ↑ 1.4 193 1

Hash Full Join (cost=6.60..21.93 rows=270 width=180) (actual time=14.779..15.183 rows=193 loops=1)

  • Hash Cond: (pf.budget_spending_kbk_id = primary_year_plans.budget_spending_kbk_id)
79. 8.692 8.692 ↑ 1.4 193 1

CTE Scan on pf (cost=0.00..5.32 rows=266 width=140) (actual time=8.368..8.692 rows=193 loops=1)

80. 0.052 6.386 ↑ 1.4 147 1

Hash (cost=4.06..4.06 rows=203 width=40) (actual time=6.386..6.386 rows=147 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
81. 6.334 6.334 ↑ 1.4 147 1

CTE Scan on primary_year_plans (cost=0.00..4.06 rows=203 width=40) (actual time=5.990..6.334 rows=147 loops=1)

82. 0.050 0.083 ↑ 1.3 192 1

Hash (cost=4.96..4.96 rows=248 width=72) (actual time=0.083..0.083 rows=192 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
83. 0.033 0.033 ↑ 1.3 192 1

CTE Scan on current_year_plans current_year_plans_1 (cost=0.00..4.96 rows=248 width=72) (actual time=0.002..0.033 rows=192 loops=1)

84. 0.107 19.903 ↑ 1.1 230 1

Hash (cost=4.86..4.86 rows=243 width=332) (actual time=19.903..19.903 rows=230 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
85. 19.796 19.796 ↑ 1.1 230 1

CTE Scan on opc (cost=0.00..4.86 rows=243 width=332) (actual time=18.997..19.796 rows=230 loops=1)

86. 0.072 14.014 ↑ 1.4 193 1

Hash (cost=5.26..5.26 rows=263 width=72) (actual time=14.014..14.014 rows=193 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
87. 13.942 13.942 ↑ 1.4 193 1

CTE Scan on budget_values (cost=0.00..5.26 rows=263 width=72) (actual time=13.542..13.942 rows=193 loops=1)

88.          

CTE uniont

89. 0.142 52.489 ↑ 2.2 1,673 1

Append (cost=0.00..156.49 rows=3,745 width=136) (actual time=48.900..52.489 rows=1,673 loops=1)

90. 50.667 50.667 ↑ 2.2 239 1

CTE Scan on merget (cost=0.00..22.74 rows=535 width=136) (actual time=48.900..50.667 rows=239 loops=1)

91. 0.265 0.265 ↑ 2.2 239 1

CTE Scan on merget merget_1 (cost=0.00..22.74 rows=535 width=136) (actual time=0.013..0.265 rows=239 loops=1)

92. 0.339 0.339 ↑ 2.2 239 1

CTE Scan on merget merget_2 (cost=0.00..22.74 rows=535 width=136) (actual time=0.013..0.339 rows=239 loops=1)

93. 0.290 0.290 ↑ 2.2 239 1

CTE Scan on merget merget_3 (cost=0.00..22.74 rows=535 width=136) (actual time=0.014..0.290 rows=239 loops=1)

94. 0.314 0.314 ↑ 2.2 239 1

CTE Scan on merget merget_4 (cost=0.00..22.74 rows=535 width=136) (actual time=0.014..0.314 rows=239 loops=1)

95. 0.258 0.258 ↑ 2.2 239 1

CTE Scan on merget merget_5 (cost=0.00..22.74 rows=535 width=136) (actual time=0.013..0.258 rows=239 loops=1)

96. 0.214 0.214 ↑ 2.2 239 1

CTE Scan on merget merget_6 (cost=0.00..20.06 rows=535 width=136) (actual time=0.008..0.214 rows=239 loops=1)