explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QqVZ

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.622 21,230.230 ↓ 1.6 6,199 1

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

2. 3.635 21,229.608 ↓ 1.6 6,199 1

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

3. 1.107 21,225.973 ↓ 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,224.458..21,225.973 rows=6,199 loops=1)

4. 3.231 21,224.866 ↓ 1.6 6,199 1

Sort (cost=1,182,697.42..1,182,707.01 rows=3,839 width=1,668) (actual time=21,224.456..21,224.866 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.635 10,297.551 ↓ 2.6 19,712 1

Group (cost=802,886.05..803,035.59 rows=7,477 width=588) (actual time=10,284.534..10,297.551 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.670 162.239 ↑ 1.0 22,430 1

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

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

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

  • Group Key: zsj_dwh.kod_zsj
10. 29.175 94.003 ↓ 1.0 116,515 1

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

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

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

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

13. 3.846 7.975 ↑ 1.0 23,303 1

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

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

15. 3.063 6.138 ↑ 1.0 22,430 1

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

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

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

17.          

CTE filtr_avg_obrat_domacnosti_metric_1

18. 34.096 10,090.716 ↓ 102.8 20,563 1

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

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

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

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

21. 1,060.915 9,824.261 ↑ 7.3 602,138 1

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

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

Sort (cost=629,724.26..640,658.34 rows=4,373,631 width=19) (actual time=8,277.316..8,763.346 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,059.291 3,907.064 ↑ 1.0 4,286,457 1

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

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

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

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

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

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

27. 0.088 0.221 ↑ 1.0 702 1

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

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

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

29. 3.872 8.015 ↑ 1.0 23,303 1

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

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

31. 3.093 6.142 ↑ 1.0 22,430 1

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

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

33. 7.254 10,285.916 ↓ 2.6 19,712 1

Sort (cost=2,057.68..2,076.37 rows=7,477 width=556) (actual time=10,284.531..10,285.916 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.390 10,278.662 ↓ 2.6 19,712 1

Hash Right Join (cost=6.50..1,576.60 rows=7,477 width=556) (actual time=10,257.921..10,278.662 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. 166.697 166.697 ↑ 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=160.336..166.697 rows=22,430 loops=1)

36. 2.860 10,097.575 ↓ 102.8 20,563 1

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

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

38.          

CTE spad_pocet_klubaru_metric_1

39. 10.838 21,216.989 ↑ 50.2 6,199 1

Sort (cost=369,307.89..370,085.29 rows=310,960 width=68) (actual time=21,216.441..21,216.989 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. 4,035.372 21,206.151 ↑ 50.2 6,199 1

HashAggregate (cost=337,828.83..340,938.43 rows=310,960 width=68) (actual time=21,203.361..21,206.151 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,012.485 17,170.779 ↓ 1.3 4,108,001 1

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

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

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

  • Hash Cond: ((segment_klienta.kod_zsj_d)::text = (zsj_d_dwh_2.kod_zsj_d)::text)
43. 732.331 732.331 ↓ 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.028..732.331 rows=6,428,346 loops=1)

44. 7.889 10,367.375 ↓ 1.8 20,535 1

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

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

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

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

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

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

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

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

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

  • Hash Cond: ((zsj_d_dwh_2.kod_zsj)::text = ("if_Q1o98XpvyKRoEAiycmVnPQ".kod_zsj)::text)
49. 2.782 2.782 ↑ 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.006..2.782 rows=23,303 loops=1)

50. 2.911 10,315.640 ↓ 98.6 19,712 1

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

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

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

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

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

53. 3.453 6.607 ↑ 1.0 22,430 1

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

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

55. 2.189 4.898 ↑ 1.0 15,061 1

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

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

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

57. 1.746 2.975 ↑ 1.0 6,251 1

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

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

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

59. 21,221.635 21,221.635 ↓ 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,216.450..21,221.635 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 : 3.192 ms
Execution time : 21,275.101 ms