explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uv9f

Settings
# exclusive inclusive rows x rows loops node
1. 0.668 50,324.948 ↑ 3,883.4 31 1

GroupAggregate (cost=8,861,722.33..8,864,731.93 rows=120,384 width=77) (actual time=50,324.021..50,324.948 rows=31 loops=1)

  • Group Key: kpi_scen1_line_context.originating_msisdn, kpi_scen1_line_context.context
2.          

CTE lines

3. 0.004 0.027 ↑ 1.0 10 1

Limit (cost=0.42..0.77 rows=10 width=21) (actual time=0.022..0.027 rows=10 loops=1)

4. 0.023 0.023 ↑ 65,682.5 10 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.020..0.023 rows=10 loops=1)

  • Heap Fetches: 0
5. 1.038 50,324.280 ↑ 27.2 4,428 1

Sort (cost=8,861,721.55..8,862,022.51 rows=120,384 width=45) (actual time=50,324.009..50,324.280 rows=4,428 loops=1)

  • Sort Key: kpi_scen1_line_context.originating_msisdn, kpi_scen1_line_context.context
  • Sort Method: quicksort Memory: 571kB
6. 2.262 50,323.242 ↑ 27.2 4,428 1

Nested Loop Left Join (cost=0.74..8,851,562.78 rows=120,384 width=45) (actual time=17,194.823..50,323.242 rows=4,428 loops=1)

7. 11,870.288 50,294.412 ↑ 27.2 4,428 1

Hash Join (cost=0.33..8,797,985.97 rows=120,384 width=77) (actual time=17,194.803..50,294.412 rows=4,428 loops=1)

  • Hash Cond: (kpi_scen1_line_context.originating_msisdn = lines.hashed_msisdn)
8. 38,424.082 38,424.082 ↑ 2.4 180,840,887 1

Seq Scan on kpi_scen1_line_context (cost=0.00..2,806,166.68 rows=435,681,100 width=77) (actual time=0.026..38,424.082 rows=180,840,887 loops=1)

  • Filter: ("left"(context, 1) <> ALL ('{1,2,3,7}'::text[]))
  • Rows Removed by Filter: 2644054
9. 0.007 0.042 ↑ 1.0 10 1

Hash (cost=0.20..0.20 rows=10 width=32) (actual time=0.042..0.042 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.035 0.035 ↑ 1.0 10 1

CTE Scan on lines (cost=0.00..0.20 rows=10 width=32) (actual time=0.025..0.035 rows=10 loops=1)

11. 26.568 26.568 ↑ 1.0 1 4,428

Index Only Scan using cell_areas_combi_cell_id_idx on cell_areas ca (cost=0.42..0.44 rows=1 width=67) (actual time=0.006..0.006 rows=1 loops=4,428)

  • Index Cond: (cell_id = replace((unnest(kpi_scen1_line_context.cell_ids)), '$'::text, '|'::text))
  • Heap Fetches: 4428