explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fMT4

Settings
# exclusive inclusive rows x rows loops node
1. 21.389 74,055.066 ↑ 341.0 1,770 1

Sort (cost=3,358,796.97..3,360,305.93 rows=603,581 width=232) (actual time=74,047.321..74,055.066 rows=1,770 loops=1)

  • Sort Key: kpi.originating_msisdn, ('2-3'::text)
  • Sort Method: quicksort Memory: 67519kB
2.          

CTE lines

3. 0.078 0.215 ↑ 1.0 500 1

Limit (cost=0.42..17.85 rows=500 width=21) (actual time=0.021..0.215 rows=500 loops=1)

4. 0.137 0.137 ↑ 1,313.7 500 1

Index Only Scan using addresses_hashed_msisdn_idx on addresses (cost=0.42..22,888.80 rows=656,825 width=21) (actual time=0.021..0.137 rows=500 loops=1)

  • Heap Fetches: 0
5. 0.431 74,033.677 ↑ 341.0 1,770 1

Append (cost=536,973.71..3,294,789.92 rows=603,581 width=232) (actual time=1,070.492..74,033.677 rows=1,770 loops=1)

6. 784.951 1,856.094 ↑ 4.4 375 1

GroupAggregate (cost=536,973.71..537,048.46 rows=1,661 width=221) (actual time=1,070.491..1,856.094 rows=375 loops=1)

  • Group Key: kpi.originating_msisdn
7. 5.841 1,071.143 ↑ 1.3 1,275 1

Sort (cost=536,973.71..536,977.87 rows=1,661 width=1,650) (actual time=1,070.094..1,071.143 rows=1,275 loops=1)

  • Sort Key: kpi.originating_msisdn
  • Sort Method: quicksort Memory: 2444kB
8. 1.014 1,065.302 ↑ 1.3 1,275 1

Nested Loop (cost=16.68..536,884.87 rows=1,661 width=1,650) (actual time=0.699..1,065.302 rows=1,275 loops=1)

9. 373.301 1,039.474 ↑ 14.6 1,306 1

Hash Join (cost=16.25..443,286.57 rows=19,074 width=1,654) (actual time=0.637..1,039.474 rows=1,306 loops=1)

  • Hash Cond: (p80.originating_msisdn = lines.hashed_msisdn)
10. 665.555 665.555 ↓ 1.0 1,687,055 1

Seq Scan on kpi_scen1_p80 p80 (cost=0.00..334,018.61 rows=1,677,861 width=1,622) (actual time=0.009..665.555 rows=1,687,055 loops=1)

11. 0.162 0.618 ↑ 1.0 500 1

Hash (cost=10.00..10.00 rows=500 width=32) (actual time=0.618..0.618 rows=500 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
12. 0.456 0.456 ↑ 1.0 500 1

CTE Scan on lines (cost=0.00..10.00 rows=500 width=32) (actual time=0.024..0.456 rows=500 loops=1)

13. 24.814 24.814 ↑ 1.0 1 1,306

Index Scan using kpi_scen1_line_context_originating_msisdn_idx on kpi_scen1_line_context kpi (cost=0.43..4.90 rows=1 width=57) (actual time=0.016..0.019 rows=1 loops=1,306)

  • Index Cond: (originating_msisdn = p80.originating_msisdn)
  • Filter: ((p80.p80 = cluster_assignment) AND ("left"(context, 1) = ANY ('{2,3}'::text[])))
  • Rows Removed by Filter: 9
14. 2.169 72,177.152 ↑ 431.5 1,395 1

Subquery Scan on *SELECT* 2 (cost=2,587,682.45..2,757,724.85 rows=601,920 width=205) (actual time=463.103..72,177.152 rows=1,395 loops=1)

15. 71,658.506 72,174.983 ↑ 431.5 1,395 1

GroupAggregate (cost=2,587,682.45..2,745,686.45 rows=601,920 width=109) (actual time=463.095..72,174.983 rows=1,395 loops=1)

  • Group Key: k.originating_msisdn, k.context, k.total_volume, k.total_volume_2g, k.total_volume_3g, k.total_volume_4g
16. 203.792 516.477 ↑ 42.6 141,296 1

Sort (cost=2,587,682.45..2,602,730.45 rows=6,019,200 width=557) (actual time=460.934..516.477 rows=141,296 loops=1)

  • Sort Key: k.originating_msisdn, k.context, k.total_volume, k.total_volume_2g, k.total_volume_3g, k.total_volume_4g
  • Sort Method: quicksort Memory: 104999kB
17. 237.924 312.685 ↑ 42.6 141,296 1

Hash Left Join (cost=5,868.29..408,022.21 rows=6,019,200 width=557) (actual time=31.231..312.685 rows=141,296 loops=1)

  • Hash Cond: (replace((unnest(k.cell_ids)), '$'::text, '|'::text) = ca.cell_id)
18. 37.496 43.630 ↑ 42.6 141,296 1

Nested Loop (cost=0.43..251,674.35 rows=6,019,200 width=101) (actual time=0.032..43.630 rows=141,296 loops=1)

19. 0.134 0.134 ↑ 1.0 500 1

CTE Scan on lines lines_1 (cost=0.00..10.00 rows=500 width=32) (actual time=0.001..0.134 rows=500 loops=1)

20. 6.000 6.000 ↑ 40.0 3 500

Index Scan using kpi_scen1_line_context_originating_msisdn_idx on kpi_scen1_line_context k (cost=0.43..442.24 rows=120 width=565) (actual time=0.008..0.012 rows=3 loops=500)

  • Index Cond: (originating_msisdn = lines_1.hashed_msisdn)
  • Filter: ("left"(context, 1) <> ALL ('{1,2,3,7}'::text[]))
  • Rows Removed by Filter: 4
21. 14.977 31.131 ↑ 1.0 46,527 1

Hash (cost=5,286.27..5,286.27 rows=46,527 width=555) (actual time=31.131..31.131 rows=46,527 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 21854kB
22. 16.154 16.154 ↑ 1.0 46,527 1

Seq Scan on cell_areas ca (cost=0.00..5,286.27 rows=46,527 width=555) (actual time=0.009..16.154 rows=46,527 loops=1)