explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6lHgu

Settings
# exclusive inclusive rows x rows loops node
1. 180.023 129,178.578 ↑ 347.0 17,393 1

Sort (cost=30,873,536.98..30,888,626.49 rows=6,035,803 width=232) (actual time=129,155.687..129,178.578 rows=17,393 loops=1)

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

CTE lines

3. 0.768 1.955 ↑ 1.0 5,000 1

Limit (cost=0.42..174.66 rows=5,000 width=21) (actual time=0.023..1.955 rows=5,000 loops=1)

4. 1.187 1.187 ↑ 131.4 5,000 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..1.187 rows=5,000 loops=1)

  • Heap Fetches: 0
5. 3.443 128,998.555 ↑ 347.0 17,393 1

Append (cost=1,674,690.07..29,473,052.03 rows=6,035,803 width=232) (actual time=7,523.269..128,998.555 rows=17,393 loops=1)

6. 5,570.970 13,100.869 ↑ 4.5 3,684 1

GroupAggregate (cost=1,674,690.07..1,675,437.65 rows=16,613 width=221) (actual time=7,523.268..13,100.869 rows=3,684 loops=1)

  • Group Key: kpi.originating_msisdn
7. 37.923 7,529.899 ↑ 1.3 12,659 1

Sort (cost=1,674,690.07..1,674,731.60 rows=16,613 width=1,650) (actual time=7,523.014..7,529.899 rows=12,659 loops=1)

  • Sort Key: kpi.originating_msisdn
  • Sort Method: quicksort Memory: 24375kB
8. 296.555 7,491.976 ↑ 1.3 12,659 1

Hash Join (cost=1,038,514.50..1,673,525.49 rows=16,613 width=1,650) (actual time=5,390.134..7,491.976 rows=12,659 loops=1)

  • Hash Cond: ((kpi.originating_msisdn = p80.originating_msisdn) AND (kpi.cluster_assignment = p80.p80))
9. 1,805.710 1,805.710 ↓ 37.0 1,644,911 1

Seq Scan on kpi_scen1_line_context kpi (cost=0.00..627,538.89 rows=44,457 width=57) (actual time=0.020..1,805.710 rows=1,644,911 loops=1)

  • Filter: ("left"(context, 1) = ANY ('{2,3}'::text[]))
  • Rows Removed by Filter: 2800815
10. 14.787 5,389.711 ↑ 14.7 12,952 1

Hash (cost=1,035,653.47..1,035,653.47 rows=190,735 width=1,654) (actual time=5,389.711..5,389.711 rows=12,952 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 19462kB
11. 2,193.362 5,374.924 ↑ 14.7 12,952 1

Hash Join (cost=692,530.87..1,035,653.47 rows=190,735 width=1,654) (actual time=3,178.084..5,374.924 rows=12,952 loops=1)

  • Hash Cond: (lines.hashed_msisdn = p80.originating_msisdn)
12. 4.415 4.415 ↑ 1.0 5,000 1

CTE Scan on lines (cost=0.00..100.00 rows=5,000 width=32) (actual time=0.026..4.415 rows=5,000 loops=1)

13. 2,552.782 3,177.147 ↓ 1.0 1,687,055 1

Hash (cost=334,018.61..334,018.61 rows=1,677,861 width=1,622) (actual time=3,177.147..3,177.147 rows=1,687,055 loops=1)

  • Buckets: 262144 Batches: 8 Memory Usage: 278744kB
14. 624.365 624.365 ↓ 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.008..624.365 rows=1,687,055 loops=1)

15. 20.108 115,894.243 ↑ 439.1 13,709 1

Subquery Scan on *SELECT* 2 (cost=25,946,547.32..27,797,448.25 rows=6,019,190 width=205) (actual time=13,277.955..115,894.243 rows=13,709 loops=1)

16. 100,730.391 115,874.135 ↑ 439.1 13,709 1

GroupAggregate (cost=25,946,547.32..27,677,064.45 rows=6,019,190 width=109) (actual time=13,277.943..115,874.135 rows=13,709 loops=1)

  • Group Key: k.originating_msisdn, k.context, k.total_volume, k.total_volume_2g, k.total_volume_3g, k.total_volume_4g
17. 6,846.974 15,143.744 ↑ 43.5 1,383,058 1

Sort (cost=25,946,547.32..26,097,027.07 rows=60,191,900 width=557) (actual time=13,277.338..15,143.744 rows=1,383,058 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: external merge Disk: 642488kB
18. 2,257.979 8,296.770 ↑ 43.5 1,383,058 1

Hash Left Join (cost=1,013,829.20..3,150,243.59 rows=60,191,900 width=557) (actual time=5,089.716..8,296.770 rows=1,383,058 loops=1)

  • Hash Cond: (replace((unnest(k.cell_ids)), '$'::text, '|'::text) = ca.cell_id)
19. 949.559 5,977.625 ↑ 43.5 1,383,058 1

Hash Join (cost=1,007,961.34..1,639,578.24 rows=60,191,900 width=101) (actual time=5,028.448..5,977.625 rows=1,383,058 loops=1)

  • Hash Cond: (lines_1.hashed_msisdn = k.originating_msisdn)
20. 0.971 0.971 ↑ 1.0 5,000 1

CTE Scan on lines lines_1 (cost=0.00..100.00 rows=5,000 width=32) (actual time=0.001..0.971 rows=5,000 loops=1)

21. 1,813.901 5,027.095 ↑ 2.4 1,801,672 1

Hash (cost=638,653.20..638,653.20 rows=4,356,811 width=565) (actual time=5,027.095..5,027.095 rows=1,801,672 loops=1)

  • Buckets: 1048576 Batches: 8 Memory Usage: 134677kB
22. 3,213.194 3,213.194 ↑ 2.4 1,801,672 1

Seq Scan on kpi_scen1_line_context k (cost=0.00..638,653.20 rows=4,356,811 width=565) (actual time=0.015..3,213.194 rows=1,801,672 loops=1)

  • Filter: ("left"(context, 1) <> ALL ('{1,2,3,7}'::text[]))
  • Rows Removed by Filter: 2644054
23. 31.654 61.166 ↑ 1.0 46,527 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 21854kB
24. 29.512 29.512 ↑ 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.010..29.512 rows=46,527 loops=1)