explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RoG : Optimization for: plan #QqVZ

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.624 21,615.902 ↓ 1.6 6,199 1

Limit (cost=1,182,697.42..1,182,793.39 rows=3,839 width=1,668) (actual time=21,614.168..21,615.902 rows=6,199 loops=1)

2. 3.650 21,615.278 ↓ 1.6 6,199 1

WindowAgg (cost=1,182,697.42..1,182,793.39 rows=3,839 width=1,668) (actual time=21,614.166..21,615.278 rows=6,199 loops=1)

3. 1.139 21,611.628 ↓ 1.6 6,199 1

Subquery Scan on inner (cost=1,182,697.42..1,182,745.40 rows=3,839 width=1,660) (actual time=21,610.060..21,611.628 rows=6,199 loops=1)

4. 3.333 21,610.489 ↓ 1.6 6,199 1

Sort (cost=1,182,697.42..1,182,707.01 rows=3,839 width=1,668) (actual time=21,610.058..21,610.489 rows=6,199 loops=1)

  • Sort Key: (COALESCE(spad_pocet_klubaru_metric_1.kod_obec)), (COALESCE(spad_pocet_klubaru_metric_1.x_min)), (COALESCE(spad_pocet_klubaru_metric_1.x_max)), (COALESCE(spad_pocet_klubaru_metric_1.y_min)), (COALESCE(spad_pocet_klubaru_metric_1.y_max)), (COALESCE(spad_pocet_klubaru_metric_1.nazev)), (COALESCE(spad_pocet_klubaru_metric_1.upper_name)), spad_pocet_klubaru_metric_1.spad_pocet_klubaru_metric_1
  • Sort Method: quicksort Memory: 1065kB
5.          

CTE if_Q1o98XpvyKRoEAiycmVnPQ

6. 11.429 10,363.439 ↓ 2.6 19,712 1

Group (cost=802,886.05..803,035.59 rows=7,477 width=588) (actual time=10,350.647..10,363.439 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. 38.132 170.244 ↑ 1.0 22,430 1

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

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

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

  • Group Key: zsj_dwh.kod_zsj
10. 30.224 101.322 ↓ 1.0 116,515 1

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

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

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

  • Hash Cond: ((demography_cz_households.kod_zsj_d)::text = (zsj_d_dwh.kod_zsj_d)::text)
12. 17.681 17.681 ↑ 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.018..17.681 rows=116,515 loops=1)

13. 4.274 8.379 ↑ 1.0 23,303 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1326kB
14. 4.105 4.105 ↑ 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.105 rows=23,303 loops=1)

15. 3.434 6.602 ↑ 1.0 22,430 1

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

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

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

17.          

CTE filtr_avg_obrat_domacnosti_metric_1

18. 34.737 10,148.750 ↓ 102.8 20,563 1

Sort (cost=793,745.07..793,745.57 rows=200 width=548) (actual time=10,147.105..10,148.750 rows=20,563 loops=1)

  • Sort Key: (COALESCE(filtr_avg_obrat_domacnosti_metric_1_0.kod_zsj))
  • Sort Method: quicksort Memory: 2247kB
19. 163.409 10,114.013 ↓ 102.8 20,563 1

HashAggregate (cost=793,735.42..793,737.42 rows=200 width=548) (actual time=10,110.044..10,114.013 rows=20,563 loops=1)

  • Group Key: COALESCE(filtr_avg_obrat_domacnosti_metric_1_0.kod_zsj)
20. 77.911 9,950.604 ↑ 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=8,342.504..9,950.604 rows=602,138 loops=1)

21. 1,121.989 9,872.693 ↑ 7.3 602,138 1

GroupAggregate (cost=629,724.26..728,130.96 rows=4,373,631 width=43) (actual time=8,342.502..9,872.693 rows=602,138 loops=1)

  • Group Key: prodejny_dwh.prodejna_id, zsj_dwh_1.kod_zsj
22. 4,538.092 8,750.704 ↑ 1.0 4,286,457 1

Sort (cost=629,724.26..640,658.34 rows=4,373,631 width=19) (actual time=8,342.489..8,750.704 rows=4,286,457 loops=1)

  • Sort Key: prodejny_dwh.prodejna_id, zsj_dwh_1.kod_zsj
  • Sort Method: quicksort Memory: 531415kB
23. 1,067.267 4,212.612 ↑ 1.0 4,286,457 1

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

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

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

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

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

  • Hash Cond: (prodeje_dwh.prodejna_id = prodejny_dwh.prodejna_id)
26. 815.951 815.951 ↑ 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..815.951 rows=4,373,556 loops=1)

27. 0.099 0.330 ↑ 1.0 702 1

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

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

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

29. 4.389 10.311 ↑ 1.0 23,303 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1326kB
30. 5.922 5.922 ↑ 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.004..5.922 rows=23,303 loops=1)

31. 3.510 8.185 ↑ 1.0 22,430 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1111kB
32. 4.675 4.675 ↑ 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.005..4.675 rows=22,430 loops=1)

33. 7.036 10,352.010 ↓ 2.6 19,712 1

Sort (cost=2,057.68..2,076.37 rows=7,477 width=556) (actual time=10,350.644..10,352.010 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. 14.284 10,344.974 ↓ 2.6 19,712 1

Hash Right Join (cost=6.50..1,576.60 rows=7,477 width=556) (actual time=10,324.475..10,344.974 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. 174.569 174.569 ↑ 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=168.334..174.569 rows=22,430 loops=1)

36. 3.220 10,156.121 ↓ 102.8 20,563 1

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

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1252kB
37. 10,152.901 10,152.901 ↓ 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=10,147.107..10,152.901 rows=20,563 loops=1)

38.          

CTE spad_pocet_klubaru_metric_1

39. 10.798 21,602.471 ↑ 50.2 6,199 1

Sort (cost=369,307.89..370,085.29 rows=310,960 width=68) (actual time=21,601.939..21,602.471 rows=6,199 loops=1)

  • Sort Key: obec_dwh.kod_obec, obec_dwh.x_min, obec_dwh.x_max, obec_dwh.y_min, obec_dwh.y_max, obec_dwh.nazev, obec_dwh.upper_name
  • Sort Method: quicksort Memory: 1064kB
40. 3,994.070 21,591.673 ↑ 50.2 6,199 1

HashAggregate (cost=337,828.83..340,938.43 rows=310,960 width=68) (actual time=21,588.949..21,591.673 rows=6,199 loops=1)

  • Group Key: obec_dwh.kod_obec, obec_dwh.x_min, obec_dwh.x_max, obec_dwh.y_min, obec_dwh.y_max, obec_dwh.nazev, obec_dwh.upper_name
41. 4,057.149 17,597.603 ↓ 1.3 4,108,001 1

HashAggregate (cost=213,444.75..244,540.77 rows=3,109,602 width=64) (actual time=16,398.579..17,597.603 rows=4,108,001 loops=1)

  • Group Key: obec_dwh.kod_obec, segment_klienta.klient_id
42. 2,105.142 13,540.454 ↓ 2.0 6,098,028 1

Hash Join (cost=2,718.88..197,896.74 rows=3,109,602 width=64) (actual time=10,435.650..13,540.454 rows=6,098,028 loops=1)

  • Hash Cond: ((segment_klienta.kod_zsj_d)::text = (zsj_d_dwh_2.kod_zsj_d)::text)
43. 999.765 999.765 ↓ 1.0 6,428,346 1

Seq Scan on segment_klienta (cost=0.00..139,977.55 rows=6,427,455 width=12) (actual time=0.026..999.765 rows=6,428,346 loops=1)

44. 7.800 10,435.547 ↓ 1.8 20,535 1

Hash (cost=2,573.23..2,573.23 rows=11,652 width=68) (actual time=10,435.547..10,435.547 rows=20,535 loops=1)

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 2358kB
45. 6.557 10,427.747 ↓ 1.8 20,535 1

Hash Join (cost=1,776.93..2,573.23 rows=11,652 width=68) (actual time=10,398.317..10,427.747 rows=20,535 loops=1)

  • Hash Cond: ((cast_obec_dwh.kod_obec)::text = (obec_dwh.kod_obec)::text)
46. 6.728 10,417.452 ↓ 1.8 20,535 1

Hash Join (cost=1,549.28..2,314.97 rows=11,652 width=15) (actual time=10,394.541..10,417.452 rows=20,535 loops=1)

  • Hash Cond: ((zsj_d_dwh_2.kod_cast_obec)::text = (cast_obec_dwh.kod_cast_obec)::text)
47. 5.770 10,404.465 ↓ 1.8 20,535 1

Hash Join (cost=996.41..1,731.50 rows=11,652 width=15) (actual time=10,388.215..10,404.465 rows=20,535 loops=1)

  • Hash Cond: ((zsj_d_dwh_2.kod_zsj)::text = (zsj_dwh_2.kod_zsj)::text)
48. 7.883 10,392.152 ↓ 1.8 20,535 1

Hash Join (cost=172.73..877.23 rows=11,652 width=538) (actual time=10,381.548..10,392.152 rows=20,535 loops=1)

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

Seq Scan on zsj_d_dwh zsj_d_dwh_2 (cost=0.00..615.03 rows=23,303 width=22) (actual time=0.012..2.750 rows=23,303 loops=1)

50. 2.940 10,381.519 ↓ 98.6 19,712 1

Hash (cost=170.23..170.23 rows=200 width=516) (actual time=10,381.519..10,381.519 rows=19,712 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1007kB
51. 8.131 10,378.579 ↓ 98.6 19,712 1

HashAggregate (cost=168.23..170.23 rows=200 width=516) (actual time=10,375.652..10,378.579 rows=19,712 loops=1)

  • Group Key: ("if_Q1o98XpvyKRoEAiycmVnPQ".kod_zsj)::text
52. 10,370.448 10,370.448 ↓ 2.6 19,712 1

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

53. 3.432 6.543 ↑ 1.0 22,430 1

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

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

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

55. 2.471 6.259 ↑ 1.0 15,061 1

Hash (cost=364.61..364.61 rows=15,061 width=14) (actual time=6.258..6.259 rows=15,061 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 805kB
56. 3.788 3.788 ↑ 1.0 15,061 1

Seq Scan on cast_obec_dwh (cost=0.00..364.61 rows=15,061 width=14) (actual time=0.014..3.788 rows=15,061 loops=1)

57. 2.024 3.738 ↑ 1.0 6,251 1

Hash (cost=149.51..149.51 rows=6,251 width=60) (actual time=3.738..3.738 rows=6,251 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 658kB
58. 1.714 1.714 ↑ 1.0 6,251 1

Seq Scan on obec_dwh (cost=0.00..149.51 rows=6,251 width=60) (actual time=0.015..1.714 rows=6,251 loops=1)

59. 21,607.156 21,607.156 ↓ 1.6 6,199 1

CTE Scan on spad_pocet_klubaru_metric_1 (cost=0.00..9,347.99 rows=3,839 width=1,668) (actual time=21,601.948..21,607.156 rows=6,199 loops=1)

  • Filter: ((COALESCE(y_max) > 48.46748632073478) AND (COALESCE(y_min) < 51.12317469379006) AND (COALESCE(x_max) > 9.604797363281248) AND (COALESCE(x_min) < 21.393127441406257))
Planning time : 5.318 ms
Execution time : 21,663.443 ms