explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JvQS

Settings
# exclusive inclusive rows x rows loops node
1. 0.630 10,731.769 ↑ 1.1 6,199 1

Limit (cost=543,742.32..543,910.92 rows=6,744 width=1,668) (actual time=10,730.013..10,731.769 rows=6,199 loops=1)

2. 3.404 10,731.139 ↑ 1.1 6,199 1

WindowAgg (cost=543,742.32..543,910.92 rows=6,744 width=1,668) (actual time=10,730.012..10,731.139 rows=6,199 loops=1)

3. 1.145 10,727.735 ↑ 1.1 6,199 1

Subquery Scan on inner (cost=543,742.32..543,826.62 rows=6,744 width=1,660) (actual time=10,726.170..10,727.735 rows=6,199 loops=1)

4. 3.263 10,726.590 ↑ 1.1 6,199 1

Sort (cost=543,742.32..543,759.18 rows=6,744 width=1,668) (actual time=10,726.168..10,726.590 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 spad_pocet_klubaru_metric_1

6. 10.794 10,718.658 ↑ 88.1 6,199 1

Sort (cost=525,526.12..526,891.78 rows=546,264 width=68) (actual time=10,718.131..10,718.658 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
7. 3,969.684 10,707.864 ↑ 88.1 6,199 1

HashAggregate (cost=468,006.60..473,469.24 rows=546,264 width=68) (actual time=10,703.669..10,707.864 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
8. 3,940.406 6,738.180 ↑ 1.3 4,108,001 1

HashAggregate (cost=249,501.08..304,127.46 rows=5,462,638 width=64) (actual time=5,505.446..6,738.180 rows=4,108,001 loops=1)

  • Group Key: obec_dwh.kod_obec, segment_klienta.klient_id
9. 2,054.473 2,797.774 ↓ 1.1 6,098,028 1

Hash Join (cost=3,483.47..222,187.89 rows=5,462,638 width=64) (actual time=57.206..2,797.774 rows=6,098,028 loops=1)

  • Hash Cond: ((segment_klienta.kod_zsj_d)::text = (zsj_d_dwh.kod_zsj_d)::text)
10. 686.161 686.161 ↓ 1.0 6,428,346 1

Seq Scan on segment_klienta (cost=0.00..139,976.16 rows=6,427,316 width=12) (actual time=0.014..686.161 rows=6,428,346 loops=1)

11. 7.434 57.140 ↓ 1.0 20,535 1

Hash (cost=3,227.49..3,227.49 rows=20,479 width=68) (actual time=57.140..57.140 rows=20,535 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2358kB
12. 6.586 49.706 ↓ 1.0 20,535 1

Hash Join (cost=2,154.72..3,227.49 rows=20,479 width=68) (actual time=20.237..49.706 rows=20,535 loops=1)

  • Hash Cond: ((cast_obec_dwh.kod_obec)::text = (obec_dwh.kod_obec)::text)
13. 6.091 40.170 ↓ 1.0 20,535 1

Hash Join (cost=1,927.07..2,946.05 rows=20,479 width=15) (actual time=17.273..40.170 rows=20,535 loops=1)

  • Hash Cond: ((zsj_d_dwh.kod_cast_obec)::text = (cast_obec_dwh.kod_cast_obec)::text)
14. 6.069 29.137 ↓ 1.0 20,535 1

Hash Semi Join (cost=1,374.20..2,339.41 rows=20,479 width=15) (actual time=12.308..29.137 rows=20,535 loops=1)

  • Hash Cond: ((zsj_d_dwh.kod_zsj)::text = ("if_Q1o98XpvyKRoEAiycmVnPQ".kod_zsj)::text)
15. 7.935 17.680 ↑ 1.0 23,303 1

Hash Join (cost=823.67..1,499.89 rows=23,303 width=29) (actual time=6.798..17.680 rows=23,303 loops=1)

  • Hash Cond: ((zsj_d_dwh.kod_zsj)::text = (zsj_dwh.kod_zsj)::text)
16. 3.074 3.074 ↑ 1.0 23,303 1

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

17. 3.440 6.671 ↑ 1.0 22,430 1

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

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

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

19. 3.010 5.388 ↑ 1.0 19,712 1

Hash (cost=304.12..304.12 rows=19,712 width=7) (actual time=5.388..5.388 rows=19,712 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1007kB
20. 2.378 2.378 ↑ 1.0 19,712 1

Seq Scan on "if_Q1o98XpvyKRoEAiycmVnPQ" (cost=0.00..304.12 rows=19,712 width=7) (actual time=0.007..2.378 rows=19,712 loops=1)

21. 2.340 4.942 ↑ 1.0 15,061 1

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

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

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

23. 1.772 2.950 ↑ 1.0 6,251 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 658kB
24. 1.178 1.178 ↑ 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.178 rows=6,251 loops=1)

25. 10,723.327 10,723.327 ↑ 1.1 6,199 1

CTE Scan on spad_pocet_klubaru_metric_1 (cost=0.00..16,421.64 rows=6,744 width=1,668) (actual time=10,718.139..10,723.327 rows=6,199 loops=1)

  • Filter: ((COALESCE(y_max) > 48.41662588881108) AND (COALESCE(y_min) < 51.175089431442956) AND (COALESCE(x_max) > 7.229003906250001) AND (COALESCE(x_min) < 23.774414062500004))
Planning time : 1.751 ms
Execution time : 10,786.266 ms