explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 59Bh

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 16,962.428 ↑ 16.0 12 1

Limit (cost=1,087,252.48..1,087,259.20 rows=192 width=76) (actual time=16,962.423..16,962.428 rows=12 loops=1)

2. 0.012 16,962.425 ↑ 16.0 12 1

WindowAgg (cost=1,087,252.48..1,087,259.20 rows=192 width=76) (actual time=16,962.422..16,962.425 rows=12 loops=1)

3. 0.002 16,962.413 ↑ 16.0 12 1

Subquery Scan on inner (cost=1,087,252.48..1,087,256.80 rows=192 width=68) (actual time=16,962.405..16,962.413 rows=12 loops=1)

4. 0.007 16,962.411 ↑ 16.0 12 1

Group (cost=1,087,252.48..1,087,254.88 rows=192 width=72) (actual time=16,962.404..16,962.411 rows=12 loops=1)

  • Group Key: (COALESCE(name.sort_month_id_0, spad_obrat_model_metric.sort_month_id_0)), (COALESCE(name."periodId", spad_obrat_model_metric."periodId")), name.name, spad_obrat_model_metric.spad_obrat_model_metric
5.          

CTE if_Q1o98XpvyKRoEAiycmVnPQ

6. 11.343 12,460.886 ↓ 2.6 19,712 1

Group (cost=802,886.05..803,035.59 rows=7,477 width=588) (actual time=12,448.184..12,460.886 rows=19,712 loops=1)

  • Group Key: (COALESCE(filtr_avg_obrat_domacnosti_metric_2.kod_zsj, filtr_avg_obrat_domacnosti_metric_1.kod_zsj)), filtr_avg_obrat_domacnosti_metric_1.filtr_avg_obrat_domacnosti_metric_1, filtr_avg_obrat_domacnosti_metric_2.filtr_avg_obrat_domacnosti_metric_2
7.          

CTE filtr_avg_obrat_domacnosti_metric_2

8. 37.090 163.014 ↑ 1.0 22,430 1

Sort (cost=7,026.73..7,082.80 rows=22,430 width=15) (actual time=161.168..163.014 rows=22,430 loops=1)

  • Sort Key: zsj_dwh.kod_zsj
  • Sort Method: quicksort Memory: 1820kB
9. 32.019 125.924 ↑ 1.0 22,430 1

HashAggregate (cost=5,181.51..5,405.81 rows=22,430 width=15) (actual time=122.413..125.924 rows=22,430 loops=1)

  • Group Key: zsj_dwh.kod_zsj
10. 29.562 93.905 ↓ 1.0 116,515 1

Hash Join (cost=1,729.99..4,601.57 rows=115,987 width=11) (actual time=14.174..93.905 rows=116,515 loops=1)

  • Hash Cond: ((zsj_d_dwh.kod_zsj)::text = (zsj_dwh.kod_zsj)::text)
11. 36.871 58.222 ↓ 1.0 116,515 1

Hash Join (cost=906.32..3,473.38 rows=115,987 width=11) (actual time=8.010..58.222 rows=116,515 loops=1)

  • Hash Cond: ((demography_cz_households.kod_zsj_d)::text = (zsj_d_dwh.kod_zsj_d)::text)
12. 13.390 13.390 ↑ 1.0 116,515 1

Seq Scan on demography_cz_households (cost=0.00..2,261.15 rows=116,515 width=12) (actual time=0.006..13.390 rows=116,515 loops=1)

13. 3.854 7.961 ↑ 1.0 23,303 1

Hash (cost=615.03..615.03 rows=23,303 width=15) (actual time=7.961..7.961 rows=23,303 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1326kB
14. 4.107 4.107 ↑ 1.0 23,303 1

Seq Scan on zsj_d_dwh (cost=0.00..615.03 rows=23,303 width=15) (actual time=0.006..4.107 rows=23,303 loops=1)

15. 3.052 6.121 ↑ 1.0 22,430 1

Hash (cost=543.30..543.30 rows=22,430 width=7) (actual time=6.120..6.121 rows=22,430 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1111kB
16. 3.069 3.069 ↑ 1.0 22,430 1

Seq Scan on zsj_dwh (cost=0.00..543.30 rows=22,430 width=7) (actual time=0.011..3.069 rows=22,430 loops=1)

17.          

CTE filtr_avg_obrat_domacnosti_metric_1

18. 34.505 12,252.307 ↓ 102.8 20,563 1

Sort (cost=793,745.07..793,745.57 rows=200 width=548) (actual time=12,250.560..12,252.307 rows=20,563 loops=1)

  • Sort Key: (COALESCE(filtr_avg_obrat_domacnosti_metric_1_0.kod_zsj))
  • Sort Method: quicksort Memory: 2247kB
19. 170.689 12,217.802 ↓ 102.8 20,563 1

HashAggregate (cost=793,735.42..793,737.42 rows=200 width=548) (actual time=12,212.846..12,217.802 rows=20,563 loops=1)

  • Group Key: COALESCE(filtr_avg_obrat_domacnosti_metric_1_0.kod_zsj)
20. 76.992 12,047.113 ↑ 7.3 602,138 1

Subquery Scan on filtr_avg_obrat_domacnosti_metric_1_0 (cost=629,724.26..771,867.27 rows=4,373,631 width=548) (actual time=10,428.536..12,047.113 rows=602,138 loops=1)

21. 1,054.332 11,970.121 ↑ 7.3 602,138 1

GroupAggregate (cost=629,724.26..728,130.96 rows=4,373,631 width=43) (actual time=10,428.534..11,970.121 rows=602,138 loops=1)

  • Group Key: prodejny_dwh.prodejna_id, zsj_dwh_1.kod_zsj
22. 5,144.060 10,915.789 ↑ 1.0 4,286,457 1

Sort (cost=629,724.26..640,658.34 rows=4,373,631 width=19) (actual time=10,428.523..10,915.789 rows=4,286,457 loops=1)

  • Sort Key: prodejny_dwh.prodejna_id, zsj_dwh_1.kod_zsj
  • Sort Method: external merge Disk: 126080kB
23. 1,594.656 5,771.729 ↑ 1.0 4,286,457 1

Hash Join (cost=1,770.79..147,304.01 rows=4,373,631 width=19) (actual time=24.759..5,771.729 rows=4,286,457 loops=1)

  • Hash Cond: ((zsj_d_dwh_1.kod_zsj)::text = (zsj_dwh_1.kod_zsj)::text)
24. 1,678.572 4,166.591 ↑ 1.0 4,286,457 1

Hash Join (cost=947.11..134,997.39 rows=4,373,631 width=19) (actual time=14.235..4,166.591 rows=4,286,457 loops=1)

  • Hash Cond: ((prodeje_dwh.kod_zsj_d)::text = (zsj_d_dwh_1.kod_zsj_d)::text)
25. 1,768.041 2,474.202 ↑ 1.0 4,359,613 1

Hash Join (cost=40.80..122,608.20 rows=4,373,631 width=20) (actual time=0.373..2,474.202 rows=4,359,613 loops=1)

  • Hash Cond: (prodeje_dwh.prodejna_id = prodejny_dwh.prodejna_id)
26. 705.802 705.802 ↑ 1.0 4,373,556 1

Seq Scan on prodeje_dwh (cost=0.00..111,017.31 rows=4,373,631 width=20) (actual time=0.005..705.802 rows=4,373,556 loops=1)

27. 0.159 0.359 ↑ 1.0 702 1

Hash (cost=32.02..32.02 rows=702 width=4) (actual time=0.359..0.359 rows=702 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
28. 0.200 0.200 ↑ 1.0 702 1

Seq Scan on prodejny_dwh (cost=0.00..32.02 rows=702 width=4) (actual time=0.005..0.200 rows=702 loops=1)

29. 6.464 13.817 ↑ 1.0 23,303 1

Hash (cost=615.03..615.03 rows=23,303 width=15) (actual time=13.817..13.817 rows=23,303 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1326kB
30. 7.353 7.353 ↑ 1.0 23,303 1

Seq Scan on zsj_d_dwh zsj_d_dwh_1 (cost=0.00..615.03 rows=23,303 width=15) (actual time=0.005..7.353 rows=23,303 loops=1)

31. 5.405 10.482 ↑ 1.0 22,430 1

Hash (cost=543.30..543.30 rows=22,430 width=7) (actual time=10.481..10.482 rows=22,430 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1111kB
32. 5.077 5.077 ↑ 1.0 22,430 1

Seq Scan on zsj_dwh zsj_dwh_1 (cost=0.00..543.30 rows=22,430 width=7) (actual time=0.006..5.077 rows=22,430 loops=1)

33. 6.976 12,449.543 ↓ 2.6 19,712 1

Sort (cost=2,057.68..2,076.37 rows=7,477 width=556) (actual time=12,448.181..12,449.543 rows=19,712 loops=1)

  • Sort Key: (COALESCE(filtr_avg_obrat_domacnosti_metric_2.kod_zsj, filtr_avg_obrat_domacnosti_metric_1.kod_zsj)), filtr_avg_obrat_domacnosti_metric_1.filtr_avg_obrat_domacnosti_metric_1, filtr_avg_obrat_domacnosti_metric_2.filtr_avg_obrat_domacnosti_metric_2
  • Sort Method: quicksort Memory: 2309kB
34. 15.224 12,442.567 ↓ 2.6 19,712 1

Hash Right Join (cost=6.50..1,576.60 rows=7,477 width=556) (actual time=12,420.556..12,442.567 rows=19,712 loops=1)

  • Hash Cond: ((filtr_avg_obrat_domacnosti_metric_2.kod_zsj)::text = (filtr_avg_obrat_domacnosti_metric_1.kod_zsj)::text)
  • Filter: (round((filtr_avg_obrat_domacnosti_metric_1.filtr_avg_obrat_domacnosti_metric_1 / NULLIF((filtr_avg_obrat_domacnosti_metric_2.filtr_avg_obrat_domacnosti_metric_2)::numeric, 0.0)), 0) >= '100'::numeric)
  • Rows Removed by Filter: 851
35. 167.968 167.968 ↑ 1.0 22,430 1

CTE Scan on filtr_avg_obrat_domacnosti_metric_2 (cost=0.00..448.60 rows=22,430 width=524) (actual time=161.170..167.968 rows=22,430 loops=1)

36. 2.913 12,259.375 ↓ 102.8 20,563 1

Hash (cost=4.00..4.00 rows=200 width=548) (actual time=12,259.375..12,259.375 rows=20,563 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1252kB
37. 12,256.462 12,256.462 ↓ 102.8 20,563 1

CTE Scan on filtr_avg_obrat_domacnosti_metric_1 (cost=0.00..4.00 rows=200 width=548) (actual time=12,250.562..12,256.462 rows=20,563 loops=1)

38.          

CTE name

39. 0.000 475.658 ↑ 16.0 12 1

Finalize GroupAggregate (cost=86,898.48..87,049.53 rows=192 width=40) (actual time=475.627..475.658 rows=12 loops=1)

  • Group Key: remodel_dim_dates_month.month_id, remodel_dim_dates_month.month_id
40. 23.276 489.565 ↑ 13.7 84 1

Gather Merge (cost=86,898.48..87,038.97 rows=1,152 width=40) (actual time=475.619..489.565 rows=84 loops=1)

  • Workers Planned: 6
  • Workers Launched: 6
41. 0.020 466.289 ↑ 16.0 12 7 / 7

Sort (cost=85,898.38..85,898.86 rows=192 width=40) (actual time=466.288..466.289 rows=12 loops=7)

  • Sort Key: remodel_dim_dates_month.month_id
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
  • Worker 2: Sort Method: quicksort Memory: 25kB
  • Worker 3: Sort Method: quicksort Memory: 25kB
  • Worker 4: Sort Method: quicksort Memory: 25kB
  • Worker 5: Sort Method: quicksort Memory: 25kB
42. 120.009 466.269 ↑ 16.0 12 7 / 7

Partial HashAggregate (cost=85,889.18..85,891.10 rows=192 width=40) (actual time=466.266..466.269 rows=12 loops=7)

  • Group Key: remodel_dim_dates_month.month_id, remodel_dim_dates_month.month_id
43. 110.924 346.260 ↑ 1.2 499,176 7 / 7

Hash Join (cost=211.81..81,520.10 rows=582,543 width=17) (actual time=2.292..346.260 rows=499,176 loops=7)

  • Hash Cond: (dim_dates.month_id = remodel_dim_dates_month.month_id)
44. 112.342 235.254 ↑ 1.2 499,176 7 / 7

Hash Join (cost=204.49..79,949.86 rows=582,543 width=4) (actual time=2.191..235.254 rows=499,176 loops=7)

  • Hash Cond: (prodeje_dwh_1.datum_id = dim_dates.date_iso)
45. 120.798 120.798 ↑ 1.2 499,176 7 / 7

Parallel Seq Scan on prodeje_dwh prodeje_dwh_1 (cost=0.00..78,215.08 rows=582,543 width=4) (actual time=0.014..120.798 rows=499,176 loops=7)

  • Filter: ((datum_id >= '2019-01-01'::date) AND (datum_id <= '2019-12-01'::date))
  • Rows Removed by Filter: 125618
46. 0.885 2.114 ↑ 1.0 5,844 7 / 7

Hash (cost=131.44..131.44 rows=5,844 width=8) (actual time=2.114..2.114 rows=5,844 loops=7)

  • Buckets: 8192 Batches: 1 Memory Usage: 293kB
47. 1.229 1.229 ↑ 1.0 5,844 7 / 7

Seq Scan on dim_dates (cost=0.00..131.44 rows=5,844 width=8) (actual time=0.008..1.229 rows=5,844 loops=7)

48. 0.035 0.082 ↑ 1.0 192 7 / 7

Hash (cost=4.92..4.92 rows=192 width=13) (actual time=0.082..0.082 rows=192 loops=7)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
49. 0.047 0.047 ↑ 1.0 192 7 / 7

Seq Scan on remodel_dim_dates_month (cost=0.00..4.92 rows=192 width=13) (actual time=0.012..0.047 rows=192 loops=7)

50.          

CTE spad_obrat_model_metric

51. 0.009 16,486.706 ↑ 16.0 12 1

Sort (cost=197,147.59..197,148.07 rows=192 width=40) (actual time=16,486.705..16,486.706 rows=12 loops=1)

  • Sort Key: remodel_dim_dates_month_1.month_id
  • Sort Method: quicksort Memory: 25kB
52. 832.111 16,486.697 ↑ 16.0 12 1

HashAggregate (cost=197,137.91..197,140.31 rows=192 width=40) (actual time=16,486.693..16,486.697 rows=12 loops=1)

  • Group Key: remodel_dim_dates_month_1.month_id, remodel_dim_dates_month_1.month_id
53. 657.764 15,654.586 ↓ 1.9 3,332,786 1

Hash Join (cost=1,336.02..184,275.35 rows=1,715,008 width=16) (actual time=12,494.328..15,654.586 rows=3,332,786 loops=1)

  • Hash Cond: (dim_dates_1.month_id = remodel_dim_dates_month_1.month_id)
54. 716.118 14,996.731 ↓ 1.9 3,332,786 1

Hash Join (cost=1,328.70..179,666.77 rows=1,715,008 width=12) (actual time=12,494.231..14,996.731 rows=3,332,786 loops=1)

  • Hash Cond: (prodeje_dwh_2.datum_id = dim_dates_1.date_iso)
55. 879.601 14,277.254 ↓ 1.9 3,332,786 1

Hash Join (cost=1,124.21..174,957.12 rows=1,715,008 width=12) (actual time=12,490.847..14,277.254 rows=3,332,786 loops=1)

  • Hash Cond: ((prodeje_dwh_2.kod_zsj_d)::text = (zsj_d_dwh_2.kod_zsj_d)::text)
56. 906.868 906.868 ↓ 1.0 3,434,919 1

Seq Scan on prodeje_dwh prodeje_dwh_2 (cost=0.00..143,819.54 rows=3,430,015 width=20) (actual time=0.034..906.868 rows=3,434,919 loops=1)

  • Filter: ((datum_id >= '2019-01-01'::date) AND (datum_id <= '2019-12-01'::date) AND ((clear)::text = 'Vyhovující'::text))
  • Rows Removed by Filter: 938637
57. 3.618 12,490.785 ↓ 1.8 20,535 1

Hash (cost=978.56..978.56 rows=11,652 width=8) (actual time=12,490.785..12,490.785 rows=20,535 loops=1)

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 1059kB
58. 6.831 12,487.167 ↓ 1.8 20,535 1

Hash Join (cost=172.73..978.56 rows=11,652 width=8) (actual time=12,477.627..12,487.167 rows=20,535 loops=1)

  • Hash Cond: ((zsj_d_dwh_2.kod_zsj)::text = ("if_Q1o98XpvyKRoEAiycmVnPQ".kod_zsj)::text)
59. 2.722 2.722 ↑ 1.0 23,303 1

Seq Scan on zsj_d_dwh zsj_d_dwh_2 (cost=0.00..615.03 rows=23,303 width=15) (actual time=0.007..2.722 rows=23,303 loops=1)

60. 2.736 12,477.614 ↓ 98.6 19,712 1

Hash (cost=170.23..170.23 rows=200 width=516) (actual time=12,477.613..12,477.614 rows=19,712 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1007kB
61. 7.476 12,474.878 ↓ 98.6 19,712 1

HashAggregate (cost=168.23..170.23 rows=200 width=516) (actual time=12,471.768..12,474.878 rows=19,712 loops=1)

  • Group Key: ("if_Q1o98XpvyKRoEAiycmVnPQ".kod_zsj)::text
62. 12,467.402 12,467.402 ↓ 2.6 19,712 1

CTE Scan on "if_Q1o98XpvyKRoEAiycmVnPQ" (cost=0.00..149.54 rows=7,477 width=516) (actual time=12,448.187..12,467.402 rows=19,712 loops=1)

63. 1.384 3.359 ↑ 1.0 5,844 1

Hash (cost=131.44..131.44 rows=5,844 width=8) (actual time=3.359..3.359 rows=5,844 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 293kB
64. 1.975 1.975 ↑ 1.0 5,844 1

Seq Scan on dim_dates dim_dates_1 (cost=0.00..131.44 rows=5,844 width=8) (actual time=0.007..1.975 rows=5,844 loops=1)

65. 0.043 0.091 ↑ 1.0 192 1

Hash (cost=4.92..4.92 rows=192 width=4) (actual time=0.091..0.091 rows=192 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
66. 0.048 0.048 ↑ 1.0 192 1

Seq Scan on remodel_dim_dates_month remodel_dim_dates_month_1 (cost=0.00..4.92 rows=192 width=4) (actual time=0.008..0.048 rows=192 loops=1)

67. 0.008 16,962.404 ↑ 16.0 12 1

Sort (cost=19.29..19.77 rows=192 width=72) (actual time=16,962.403..16,962.404 rows=12 loops=1)

  • Sort Key: (COALESCE(name.sort_month_id_0, spad_obrat_model_metric.sort_month_id_0)) NULLS FIRST, (COALESCE(name."periodId", spad_obrat_model_metric."periodId")), name.name, spad_obrat_model_metric.spad_obrat_model_metric
  • Sort Method: quicksort Memory: 25kB
68. 0.017 16,962.396 ↑ 16.0 12 1

Hash Full Join (cost=6.72..12.01 rows=192 width=72) (actual time=16,962.353..16,962.396 rows=12 loops=1)

  • Hash Cond: ((name."periodId" = spad_obrat_model_metric."periodId") AND (name.sort_month_id_0 = spad_obrat_model_metric.sort_month_id_0))
69. 475.663 475.663 ↑ 16.0 12 1

CTE Scan on name (cost=0.00..3.84 rows=192 width=40) (actual time=475.629..475.663 rows=12 loops=1)

70. 0.005 16,486.716 ↑ 16.0 12 1

Hash (cost=3.84..3.84 rows=192 width=40) (actual time=16,486.716..16,486.716 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
71. 16,486.711 16,486.711 ↑ 16.0 12 1

CTE Scan on spad_obrat_model_metric (cost=0.00..3.84 rows=192 width=40) (actual time=16,486.707..16,486.711 rows=12 loops=1)

Planning time : 3.351 ms
Execution time : 16,995.032 ms