explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wCTS

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=5,149,736.31..5,149,736.33 rows=1 width=9) (actual rows= loops=)

  • Group Key: ((kpi_post.user_profile = kpi_scen1_final_2.user_profile))
2. 0.000 0.000 ↓ 0.0

Sort (cost=5,149,736.31..5,149,736.31 rows=1 width=1) (actual rows= loops=)

  • Sort Key: ((kpi_post.user_profile = kpi_scen1_final_2.user_profile))
3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=181,924.19..5,149,736.30 rows=1 width=1) (actual rows= loops=)

  • Join Filter: (a_post.hashed_msisdn = l_post.line_identifier)
4. 0.000 0.000 ↓ 0.0

Nested Loop (cost=181,923.76..5,149,735.83 rows=1 width=123) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=181,923.34..5,149,735.31 rows=1 width=102) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Merge Join (cost=181,922.91..5,149,731.30 rows=3 width=113) (actual rows= loops=)

  • Merge Cond: (kpi_scen1_final_2.originating_msisdn = addresses.hashed_msisdn)
  • Join Filter: (kpi_post.line_device_capability = kpi_scen1_final_2.line_device_capability)
7. 0.000 0.000 ↓ 0.0

Unique (cost=0.56..1,590,020.47 rows=47,398 width=1,405) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Index Scan using kpi_scen1_final_2_originating_msisdn_idx on kpi_scen1_final_2 (cost=0.56..1,575,515.79 rows=5,801,871 width=1,405) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Materialize (cost=181,922.36..3,559,085.03 rows=10,800 width=86) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Merge Join (cost=181,922.36..3,559,058.03 rows=10,800 width=86) (actual rows= loops=)

  • Merge Cond: (kpi_post.originating_msisdn = addresses.hashed_msisdn)
11. 0.000 0.000 ↓ 0.0

Subquery Scan on kpi_post (cost=0.56..3,208,931.58 rows=21,600 width=33) (actual rows= loops=)

  • Filter: (kpi_post.line_device_capability IS NOT NULL)
12. 0.000 0.000 ↓ 0.0

Unique (cost=0.56..3,208,714.49 rows=21,709 width=1,437) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Index Scan using kpi_scen1_final_2_originating_msisdn_context_idx on kpi_scen1_final_2 kpi_scen1_final_2_1 (cost=0.56..3,197,289.22 rows=4,570,109 width=1,437) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Index Scan using addresses_hashed_msisdn_idx on addresses (cost=181,921.80..348,702.78 rows=504,666 width=53) (actual rows= loops=)

  • Filter: (NOT (hashed SubPlan 1))
15.          

SubPlan (forIndex Scan)

16. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=0.42..179,398.05 rows=1,009,331 width=21) (actual rows= loops=)

  • Group Key: addresses_1.hashed_msisdn
  • Filter: (count(1) > 1)
17. 0.000 0.000 ↓ 0.0

Index Only Scan using addresses_hashed_msisdn_idx on addresses addresses_1 (cost=0.42..164,258.08 rows=1,009,331 width=21) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Index Scan using addresses_hashed_msisdn_idx on addresses a_post (cost=0.42..1.33 rows=1 width=53) (actual rows= loops=)

  • Index Cond: (hashed_msisdn = addresses.hashed_msisdn)
  • Filter: ((geom_proj && st_expand(addresses.geom_proj, '50'::double precision)) AND (addresses.geom_proj && st_expand(geom_proj, '50'::double precision)) AND _st_dwithin(geom_proj, addresses.geom_proj, '50'::double precision))
19. 0.000 0.000 ↓ 0.0

Index Only Scan using lines_snapshot_usage_line_identifier_idx on lines_snapshot_usage l_pre (cost=0.42..0.51 rows=1 width=21) (actual rows= loops=)

  • Index Cond: (line_identifier = a_post.hashed_msisdn)
20. 0.000 0.000 ↓ 0.0

Index Only Scan using lines_snapshot_usage_line_identifier_idx on lines_snapshot_usage l_post (cost=0.42..0.46 rows=1 width=21) (actual rows= loops=)

  • Index Cond: (line_identifier = l_pre.line_identifier)