explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EJGe

Settings
# exclusive inclusive rows x rows loops node
1. 2,690.074 52,918.152 ↑ 3.0 4 1

GroupAggregate (cost=9,904,882.79..9,906,915.63 rows=12 width=88) (actual time=50,294.938..52,918.152 rows=4 loops=1)

  • Group Key: kpi_scen1_line_context.context
2.          

CTE lines

3. 0.003 0.030 ↑ 1.0 10 1

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

4. 0.027 0.027 ↑ 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.024..0.027 rows=10 loops=1)

  • Heap Fetches: 0
5. 2.839 50,228.078 ↑ 30.6 4,428 1

Sort (cost=9,904,882.01..9,905,220.79 rows=135,511 width=512) (actual time=50,226.582..50,228.078 rows=4,428 loops=1)

  • Sort Key: kpi_scen1_line_context.context
  • Sort Method: quicksort Memory: 2888kB
6. 2.757 50,225.239 ↑ 30.6 4,428 1

Nested Loop Left Join (cost=0.74..9,893,331.02 rows=135,511 width=512) (actual time=17,361.061..50,225.239 rows=4,428 loops=1)

7. 11,881.227 50,195.914 ↑ 30.6 4,428 1

Hash Join (cost=0.33..9,833,022.70 rows=135,511 width=56) (actual time=17,361.041..50,195.914 rows=4,428 loops=1)

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

Seq Scan on kpi_scen1_line_context (cost=0.00..3,088,301.51 rows=490,426,600 width=77) (actual time=0.028..38,314.641 rows=180,840,887 loops=1)

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

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

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

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

11. 26.568 26.568 ↑ 1.0 1 4,428

Index Scan using cell_areas_combi_cell_id_idx on cell_areas ca (cost=0.42..0.44 rows=1 width=555) (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))