explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KuiL

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 17,707.104 ↑ 119.4 52 1

Limit (cost=619,883.57..620,287.28 rows=6,211 width=2,700) (actual time=17,707.086..17,707.104 rows=52 loops=1)

2. 0.040 17,707.096 ↑ 119.4 52 1

WindowAgg (cost=619,883.57..620,287.28 rows=6,211 width=2,700) (actual time=17,707.084..17,707.096 rows=52 loops=1)

3. 0.009 17,707.056 ↑ 119.4 52 1

Subquery Scan on inner (cost=619,883.57..620,209.65 rows=6,211 width=2,692) (actual time=17,706.998..17,707.056 rows=52 loops=1)

4. 0.052 17,707.047 ↑ 119.4 52 1

Group (cost=619,883.57..620,147.54 rows=6,211 width=2,708) (actual time=17,706.997..17,707.047 rows=52 loops=1)

  • Group Key: (COALESCE(penetrace_klientu_metric_2.kod_okres, penetrace_klientu_metric_1.kod_okres)), (COALESCE(penetrace_klientu_metric_2.x_min, penetrace_klientu_metric_1.x_min)), (COALESCE(penetrace_klientu_metric_2.x_max, penetrace_klientu_metric_1.x_max)), (COALESCE(penetrace_klientu_metric_2.y_min, penetrace_klientu_metric_1.y_min)), (COALESCE(penetrace_klientu_metric_2.y_max, penetrace_klientu_metric_1.y_max)), (COALESCE(penetrace_klientu_metric_2.nazev, penetrace_klientu_metric_1.nazev)), (COALESCE(penetrace_klientu_metric_2.popis, penetrace_klientu_metric_1.popis)), (COALESCE(penetrace_klientu_metric_2.kod_kraj, penetrace_klientu_metric_1.kod_kraj)), (COALESCE(penetrace_klientu_metric_2.upper_name, penetrace_klientu_metric_1.upper_name)), penetrace_klientu_metric_1.penetrace_klientu_metric_1, penetrace_klientu_metric_2.penetrace_klientu_metric_2
5.          

CTE penetrace_klientu_metric_2

6. 0.047 583.592 ↑ 1.0 77 1

Sort (cost=17,888.50..17,888.69 rows=77 width=84) (actual time=583.586..583.592 rows=77 loops=1)

  • Sort Key: okres_dwh_1.kod_okres, okres_dwh_1.x_min, okres_dwh_1.x_max, okres_dwh_1.y_min, okres_dwh_1.y_max, okres_dwh_1.nazev, okres_dwh_1.popis, okres_dwh_1.kod_kraj, okres_dwh_1.upper_name
  • Sort Method: quicksort Memory: 35kB
7. 0.000 583.545 ↑ 1.0 77 1

Finalize GroupAggregate (cost=17,846.90..17,886.08 rows=77 width=84) (actual time=583.390..583.545 rows=77 loops=1)

  • Group Key: okres_dwh_1.kod_okres
8. 458.333 584.165 ↑ 1.1 276 1

Gather Merge (cost=17,846.90..17,883.77 rows=308 width=84) (actual time=583.380..584.165 rows=276 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
9. 0.090 125.832 ↑ 1.4 55 5 / 5

Sort (cost=16,846.84..16,847.03 rows=77 width=84) (actual time=125.828..125.832 rows=55 loops=5)

  • Sort Key: okres_dwh_1.kod_okres
  • Sort Method: quicksort Memory: 35kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 31kB
  • Worker 2: Sort Method: quicksort Memory: 35kB
  • Worker 3: Sort Method: quicksort Memory: 35kB
10. 34.187 125.742 ↑ 1.4 55 5 / 5

Partial HashAggregate (cost=16,843.65..16,844.42 rows=77 width=84) (actual time=125.729..125.742 rows=55 loops=5)

  • Group Key: okres_dwh_1.kod_okres
11. 62.085 91.555 ↑ 1.2 177,103 5 / 5

Parallel Hash Join (cost=1,445.10..15,737.47 rows=221,236 width=80) (actual time=9.493..91.555 rows=177,103 loops=5)

  • Hash Cond: ((demography_cz_age_1_1.kod_zsj_d)::text = (zsj_d_dwh_1.kod_zsj_d)::text)
12. 20.053 20.053 ↑ 1.0 221,378 4 / 5

Parallel Seq Scan on demography_cz_age_1_1 (cost=0.00..12,160.78 rows=221,378 width=12) (actual time=0.015..25.066 rows=221,378 loops=4)

13. 2.254 9.417 ↑ 2.9 4,661 5 / 5

Parallel Hash (cost=1,273.75..1,273.75 rows=13,708 width=84) (actual time=9.417..9.417 rows=4,661 loops=5)

  • Buckets: 32,768 Batches: 1 Memory Usage: 3,104kB
14. 1.478 7.163 ↓ 1.7 23,303 1 / 5

Hash Join (cost=644.71..1,273.75 rows=13,708 width=84) (actual time=7.813..35.816 rows=23,303 loops=1)

  • Hash Cond: ((obec_dwh_1.kod_okres)::text = (okres_dwh_1.kod_okres)::text)
15. 1.515 5.675 ↓ 1.7 23,303 1 / 5

Hash Join (cost=640.98..1,232.06 rows=13,708 width=14) (actual time=7.753..28.375 rows=23,303 loops=1)

  • Hash Cond: ((cast_obec_dwh_1.kod_obec)::text = (obec_dwh_1.kod_obec)::text)
16. 2.026 3.726 ↓ 1.7 23,303 1 / 5

Parallel Hash Join (cost=413.33..968.40 rows=13,708 width=15) (actual time=5.565..18.628 rows=23,303 loops=1)

  • Hash Cond: ((zsj_d_dwh_1.kod_cast_obec)::text = (cast_obec_dwh_1.kod_cast_obec)::text)
17. 0.600 0.600 ↓ 1.7 23,303 1 / 5

Parallel Seq Scan on zsj_d_dwh_1 (cost=0.00..519.08 rows=13,708 width=15) (actual time=0.004..2.999 rows=23,303 loops=1)

18. 0.553 1.100 ↓ 1.7 15,061 1 / 5

Parallel Hash (cost=302.59..302.59 rows=8,859 width=14) (actual time=5.499..5.500 rows=15,061 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 864kB
19. 0.547 0.547 ↓ 1.7 15,061 1 / 5

Parallel Seq Scan on cast_obec_dwh_1 (cost=0.00..302.59 rows=8,859 width=14) (actual time=0.004..2.733 rows=15,061 loops=1)

20. 0.201 0.434 ↑ 1.0 6,251 1 / 5

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 339kB
21. 0.233 0.233 ↑ 1.0 6,251 1 / 5

Seq Scan on obec_dwh_1 (cost=0.00..149.51 rows=6,251 width=13) (actual time=0.006..1.167 rows=6,251 loops=1)

22. 0.005 0.011 ↑ 1.0 77 1 / 5

Hash (cost=2.77..2.77 rows=77 width=76) (actual time=0.053..0.053 rows=77 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
23. 0.005 0.005 ↑ 1.0 77 1 / 5

Seq Scan on okres_dwh_1 (cost=0.00..2.77 rows=77 width=76) (actual time=0.010..0.026 rows=77 loops=1)

24.          

CTE penetrace_klientu_metric_1

25. 0.118 17,123.111 ↑ 6,533.2 77 1

Sort (cost=573,302.55..574,560.19 rows=503,057 width=84) (actual time=17,123.105..17,123.111 rows=77 loops=1)

  • Sort Key: okres_dwh_1_1.kod_okres, okres_dwh_1_1.x_min, okres_dwh_1_1.x_max, okres_dwh_1_1.y_min, okres_dwh_1_1.y_max, okres_dwh_1_1.nazev, okres_dwh_1_1.popis, okres_dwh_1_1.kod_kraj, okres_dwh_1_1.upper_name
  • Sort Method: quicksort Memory: 35kB
26. 3,596.605 17,122.993 ↑ 6,533.2 77 1

HashAggregate (cost=520,631.57..525,662.14 rows=503,057 width=84) (actual time=17,119.887..17,122.993 rows=77 loops=1)

  • Group Key: okres_dwh_1_1.kod_okres, okres_dwh_1_1.x_min, okres_dwh_1_1.x_max, okres_dwh_1_1.y_min, okres_dwh_1_1.y_max, okres_dwh_1_1.nazev, okres_dwh_1_1.popis, okres_dwh_1_1.kod_kraj, okres_dwh_1_1.upper_name
27. 3,326.075 13,526.388 ↑ 1.6 3,160,748 1

HashAggregate (cost=294,256.06..344,561.73 rows=5,030,567 width=88) (actual time=12,407.524..13,526.388 rows=3,160,748 loops=1)

  • Group Key: okres_dwh_1_1.kod_okres, klienti_dwh_3.client_id
28. 1,549.355 10,200.313 ↓ 1.0 5,037,418 1

Hash Join (cost=49,643.99..269,103.22 rows=5,030,567 width=88) (actual time=672.776..10,200.313 rows=5,037,418 loops=1)

  • Hash Cond: ((obec_dwh_1_1.kod_okres)::text = (okres_dwh_1_1.kod_okres)::text)
29. 1,372.519 8,650.904 ↓ 1.0 5,037,418 1

Hash Join (cost=49,640.25..255,167.43 rows=5,030,567 width=18) (actual time=672.710..8,650.904 rows=5,037,418 loops=1)

  • Hash Cond: ((cast_obec_dwh_1_1.kod_obec)::text = (obec_dwh_1_1.kod_obec)::text)
30. 1,582.174 7,276.121 ↓ 1.0 5,037,418 1

Hash Join (cost=49,412.61..241,725.59 rows=5,030,567 width=19) (actual time=670.415..7,276.121 rows=5,037,418 loops=1)

  • Hash Cond: ((zsj_d_dwh_1_1.kod_cast_obec)::text = (cast_obec_dwh_1_1.kod_cast_obec)::text)
31. 1,665.711 5,688.684 ↓ 1.0 5,037,418 1

Hash Join (cost=48,859.74..227,963.76 rows=5,030,567 width=19) (actual time=665.088..5,688.684 rows=5,037,418 loops=1)

  • Hash Cond: ((klienti_distinct_dwh_3.kod_zsj_d)::text = (zsj_d_dwh_1_1.kod_zsj_d)::text)
32. 2,637.266 4,013.949 ↓ 1.0 5,037,422 1

Hash Join (cost=47,953.42..213,849.80 rows=5,030,567 width=20) (actual time=655.944..4,013.949 rows=5,037,422 loops=1)

  • Hash Cond: ((klienti_dwh_3.coords_key)::text = (klienti_distinct_dwh_3.coords_key)::text)
33. 725.987 725.987 ↓ 1.0 5,249,530 1

Seq Scan on klienti_dwh_3 (cost=0.00..152,118.40 rows=5,248,740 width=45) (actual time=0.006..725.987 rows=5,249,530 loops=1)

34. 427.811 650.696 ↑ 1.0 1,416,063 1

Hash (cost=30,252.63..30,252.63 rows=1,416,063 width=41) (actual time=650.696..650.696 rows=1,416,063 loops=1)

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 118,410kB
35. 222.885 222.885 ↑ 1.0 1,416,063 1

Seq Scan on klienti_distinct_dwh_3 (cost=0.00..30,252.63 rows=1,416,063 width=41) (actual time=0.008..222.885 rows=1,416,063 loops=1)

36. 4.583 9.024 ↑ 1.0 23,303 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,326kB
37. 4.441 4.441 ↑ 1.0 23,303 1

Seq Scan on zsj_d_dwh_1 zsj_d_dwh_1_1 (cost=0.00..615.03 rows=23,303 width=15) (actual time=0.008..4.441 rows=23,303 loops=1)

38. 2.527 5.263 ↑ 1.0 15,061 1

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

  • Buckets: 16,384 Batches: 1 Memory Usage: 805kB
39. 2.736 2.736 ↑ 1.0 15,061 1

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

40. 1.094 2.264 ↑ 1.0 6,251 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 339kB
41. 1.170 1.170 ↑ 1.0 6,251 1

Seq Scan on obec_dwh_1 obec_dwh_1_1 (cost=0.00..149.51 rows=6,251 width=13) (actual time=0.006..1.170 rows=6,251 loops=1)

42. 0.029 0.054 ↑ 1.0 77 1

Hash (cost=2.77..2.77 rows=77 width=76) (actual time=0.054..0.054 rows=77 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
43. 0.025 0.025 ↑ 1.0 77 1

Seq Scan on okres_dwh_1 okres_dwh_1_1 (cost=0.00..2.77 rows=77 width=76) (actual time=0.010..0.025 rows=77 loops=1)

44. 0.036 17,706.995 ↑ 119.4 52 1

Sort (cost=27,434.69..27,450.22 rows=6,211 width=2,676) (actual time=17,706.991..17,706.995 rows=52 loops=1)

  • Sort Key: (COALESCE(penetrace_klientu_metric_2.kod_okres, penetrace_klientu_metric_1.kod_okres)), (COALESCE(penetrace_klientu_metric_2.x_min, penetrace_klientu_metric_1.x_min)), (COALESCE(penetrace_klientu_metric_2.x_max, penetrace_klientu_metric_1.x_max)), (COALESCE(penetrace_klientu_metric_2.y_min, penetrace_klientu_metric_1.y_min)), (COALESCE(penetrace_klientu_metric_2.y_max, penetrace_klientu_metric_1.y_max)), (COALESCE(penetrace_klientu_metric_2.nazev, penetrace_klientu_metric_1.nazev)), (COALESCE(penetrace_klientu_metric_2.popis, penetrace_klientu_metric_1.popis)), (COALESCE(penetrace_klientu_metric_2.kod_kraj, penetrace_klientu_metric_1.kod_kraj)), (COALESCE(penetrace_klientu_metric_2.upper_name, penetrace_klientu_metric_1.upper_name)), penetrace_klientu_metric_1.penetrace_klientu_metric_1, penetrace_klientu_metric_2.penetrace_klientu_metric_2
  • Sort Method: quicksort Memory: 32kB
45. 0.159 17,706.959 ↑ 119.4 52 1

Hash Full Join (cost=4.04..27,043.38 rows=6,211 width=2,676) (actual time=17,706.803..17,706.959 rows=52 loops=1)

  • Hash Cond: (((penetrace_klientu_metric_1.kod_okres)::text = (penetrace_klientu_metric_2.kod_okres)::text) AND (penetrace_klientu_metric_1.x_min = penetrace_klientu_metric_2.x_min) AND (penetrace_klientu_metric_1.x_max = penetrace_klientu_metric_2.x_max) AND (penetrace_klientu_metric_1.y_min = penetrace_klientu_metric_2.y_min) AND (penetrace_klientu_metric_1.y_max = penetrace_klientu_metric_2.y_max) AND ((penetrace_klientu_metric_1.nazev)::text = (penetrace_klientu_metric_2.nazev)::text) AND ((penetrace_klientu_metric_1.popis)::text = (penetrace_klientu_metric_2.popis)::text) AND ((penetrace_klientu_metric_1.kod_kraj)::text = (penetrace_klientu_metric_2.kod_kraj)::text) AND ((penetrace_klientu_metric_1.upper_name)::text = (penetrace_klientu_metric_2.upper_name)::text))
  • Filter: ((COALESCE(penetrace_klientu_metric_2.y_max, penetrace_klientu_metric_1.y_max) > 47.762977664300855) AND (COALESCE(penetrace_klientu_metric_2.y_min, penetrace_klientu_metric_1.y_min) < 49.93885746773825) AND (COALESCE(penetrace_klientu_metric_2.x_max, penetrace_klientu_metric_1.x_max) > 11.6180419921875) AND (COALESCE(penetrace_klientu_metric_2.x_min, penetrace_klientu_metric_1.x_min) < 19.890747070312504))
  • Rows Removed by Filter: 25
46. 17,123.128 17,123.128 ↑ 6,533.2 77 1

CTE Scan on penetrace_klientu_metric_1 (cost=0.00..10,061.14 rows=503,057 width=2,668) (actual time=17,123.107..17,123.128 rows=77 loops=1)

47. 0.063 583.672 ↑ 1.0 77 1

Hash (cost=1.54..1.54 rows=77 width=2,668) (actual time=583.672..583.672 rows=77 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
48. 583.609 583.609 ↑ 1.0 77 1

CTE Scan on penetrace_klientu_metric_2 (cost=0.00..1.54 rows=77 width=2,668) (actual time=583.588..583.609 rows=77 loops=1)

Planning time : 2.658 ms
Execution time : 17,760.387 ms