explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MENo

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 20,131.957 ↓ 200.0 200 1

Limit (cost=4,094,260.72..4,094,260.74 rows=1 width=74) (actual time=20,131.891..20,131.957 rows=200 loops=1)

2. 0.039 20,131.944 ↓ 200.0 200 1

Unique (cost=4,094,260.72..4,094,260.74 rows=1 width=74) (actual time=20,131.890..20,131.944 rows=200 loops=1)

3. 126.942 20,131.905 ↓ 200.0 200 1

Sort (cost=4,094,260.72..4,094,260.72 rows=1 width=74) (actual time=20,131.889..20,131.905 rows=200 loops=1)

  • Sort Key: l_post.line_identifier, kpi_post.line_device_capability, kpi_post.user_profile, kpi_post.line_total_volume, kpi_post.week_count, kpi_scen1_final_2.user_profile, kpi_scen1_final_2.line_total_volume, kpi_scen1_final_2.week_count
  • Sort Method: quicksort Memory: 34403kB
4. 660.575 20,004.963 ↓ 200,952.0 200,952 1

Nested Loop (cost=181,924.06..4,094,260.71 rows=1 width=74) (actual time=1,655.424..20,004.963 rows=200,952 loops=1)

  • Join Filter: ((a_post.hashed_msisdn = addresses.hashed_msisdn) AND (a_post.geom_proj && st_expand(addresses.geom_proj, '50'::double precision)) AND (addresses.geom_proj && st_expand(a_post.geom_proj, '50'::double precision)) AND _st_dwithin(a_post.geom_proj, addresses.geom_proj, '50'::double precision))
  • Rows Removed by Join Filter: 34473
5. 103.345 16,283.863 ↓ 235,425.0 235,425 1

Nested Loop (cost=2.27..3,912,338.44 rows=1 width=190) (actual time=0.266..16,283.863 rows=235,425 loops=1)

  • Join Filter: (l_post.line_identifier = a_post.hashed_msisdn)
6. 158.820 14,767.968 ↓ 235,425.0 235,425 1

Nested Loop (cost=1.84..3,912,337.62 rows=1 width=137) (actual time=0.244..14,767.968 rows=235,425 loops=1)

7. 132.096 13,432.023 ↓ 235,425.0 235,425 1

Nested Loop (cost=1.42..3,912,337.10 rows=1 width=116) (actual time=0.224..13,432.023 rows=235,425 loops=1)

  • Join Filter: (kpi_post.originating_msisdn = l_post.line_identifier)
8. 937.953 12,122.802 ↓ 235,425.0 235,425 1

Merge Join (cost=0.99..3,912,336.39 rows=1 width=95) (actual time=0.185..12,122.802 rows=235,425 loops=1)

  • Merge Cond: (kpi_scen1_final_2.originating_msisdn = kpi_post.originating_msisdn)
  • Join Filter: ((kpi_post.line_device_capability = kpi_scen1_final_2.line_device_capability) AND (replace(replace(kpi_post.user_profile, 'high '::text, ''::text), 'low '::text, ''::text) = replace(replace(kpi_scen1_final_2.user_profile, 'high '::text, ''::text), 'low '::text, ''::text)))
  • Rows Removed by Join Filter: 105470
9. 705.259 6,077.809 ↓ 21.3 1,009,321 1

Unique (cost=0.56..1,590,020.47 rows=47,398 width=1,383) (actual time=0.028..6,077.809 rows=1,009,321 loops=1)

10. 5,372.550 5,372.550 ↑ 1.0 5,801,870 1

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,383) (actual time=0.025..5,372.550 rows=5,801,870 loops=1)

11. 86.371 5,107.040 ↓ 27.9 602,477 1

Materialize (cost=0.43..2,321,129.45 rows=21,600 width=48) (actual time=0.025..5,107.040 rows=602,477 loops=1)

12. 76.162 5,020.669 ↓ 27.9 602,477 1

Subquery Scan on kpi_post (cost=0.43..2,321,075.45 rows=21,600 width=48) (actual time=0.022..5,020.669 rows=602,477 loops=1)

  • Filter: (kpi_post.line_device_capability IS NOT NULL)
  • Rows Removed by Filter: 54348
13. 542.424 4,944.507 ↓ 30.3 656,825 1

Unique (cost=0.43..2,320,858.36 rows=21,709 width=1,416) (actual time=0.021..4,944.507 rows=656,825 loops=1)

14. 4,402.083 4,402.083 ↑ 1.0 4,569,784 1

Index Scan using kpi_scen1_final_2_originating_msisdn_idx on kpi_scen1_final_2 kpi_scen1_final_2_1 (cost=0.43..2,309,433.90 rows=4,569,784 width=1,416) (actual time=0.019..4,402.083 rows=4,569,784 loops=1)

15. 1,177.125 1,177.125 ↑ 1.0 1 235,425

Index Only Scan using lines_snapshot_usage_line_identifier_idx on lines_snapshot_usage l_post (cost=0.42..0.70 rows=1 width=21) (actual time=0.005..0.005 rows=1 loops=235,425)

  • Index Cond: (line_identifier = kpi_scen1_final_2.originating_msisdn)
  • Heap Fetches: 0
16. 1,177.125 1,177.125 ↑ 1.0 1 235,425

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 time=0.005..0.005 rows=1 loops=235,425)

  • Index Cond: (line_identifier = l_post.line_identifier)
  • Heap Fetches: 235425
17. 1,412.550 1,412.550 ↑ 1.0 1 235,425

Index Scan using addresses_hashed_msisdn_idx on addresses a_post (cost=0.42..0.81 rows=1 width=53) (actual time=0.006..0.006 rows=1 loops=235,425)

  • Index Cond: (hashed_msisdn = l_pre.line_identifier)
18. 1,407.298 3,060.525 ↑ 1.0 1 235,425

Index Scan using addresses_hashed_msisdn_idx on addresses (cost=181,921.80..181,921.99 rows=1 width=53) (actual time=0.013..0.013 rows=1 loops=235,425)

  • Index Cond: (hashed_msisdn = l_pre.line_identifier)
  • Filter: (NOT (hashed SubPlan 1))
19.          

SubPlan (forIndex Scan)

20. 482.682 1,653.227 ↑ 112,147.9 9 1

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

  • Group Key: addresses_1.hashed_msisdn
  • Filter: (count(1) > 1)
  • Rows Removed by Filter: 1009313
21. 1,170.545 1,170.545 ↑ 1.0 1,009,331 1

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 time=0.016..1,170.545 rows=1,009,331 loops=1)

  • Heap Fetches: 1009331