explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DKqu

Settings
# exclusive inclusive rows x rows loops node
1. 2.195 246,728.795 ↑ 55,894.2 1,490 1

Sort (cost=40,036,419,804.89..40,036,628,010.70 rows=83,282,323 width=288) (actual time=246,728.708..246,728.795 rows=1,490 loops=1)

  • Sort Key: "*SELECT* 1".line_identifier, "*SELECT* 1".context, "*SELECT* 1".post_import
  • Sort Method: quicksort Memory: 431kB
2.          

CTE comparable_lines

3. 0.010 21,666.257 ↓ 200.0 200 1

Limit (cost=4,982,116.84..4,982,116.87 rows=1 width=74) (actual time=21,666.188..21,666.257 rows=200 loops=1)

4. 0.046 21,666.247 ↓ 200.0 200 1

Unique (cost=4,982,116.84..4,982,116.87 rows=1 width=74) (actual time=21,666.187..21,666.247 rows=200 loops=1)

5. 129.067 21,666.201 ↓ 200.0 200 1

Sort (cost=4,982,116.84..4,982,116.85 rows=1 width=74) (actual time=21,666.186..21,666.201 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
6. 717.646 21,537.134 ↓ 200,952.0 200,952 1

Nested Loop (cost=181,924.19..4,982,116.83 rows=1 width=74) (actual time=1,604.677..21,537.134 rows=200,952 loops=1)

  • Join Filter: ((a_post.hashed_msisdn = addresses_1.hashed_msisdn) AND (a_post.geom_proj && st_expand(addresses_1.geom_proj, '50'::double precision)) AND (addresses_1.geom_proj && st_expand(a_post.geom_proj, '50'::double precision)) AND _st_dwithin(a_post.geom_proj, addresses_1.geom_proj, '50'::double precision))
  • Rows Removed by Join Filter: 34473
7. 202.468 17,758.963 ↓ 235,425.0 235,425 1

Nested Loop (cost=2.39..4,800,194.57 rows=1 width=190) (actual time=0.245..17,758.963 rows=235,425 loops=1)

  • Join Filter: (l_post.line_identifier = a_post.hashed_msisdn)
8. 12.160 16,143.945 ↓ 235,425.0 235,425 1

Nested Loop (cost=1.97..4,800,193.75 rows=1 width=137) (actual time=0.222..16,143.945 rows=235,425 loops=1)

9. 214.772 14,719.235 ↓ 235,425.0 235,425 1

Nested Loop (cost=1.54..4,800,193.23 rows=1 width=116) (actual time=0.201..14,719.235 rows=235,425 loops=1)

  • Join Filter: (kpi_post.originating_msisdn = l_post.line_identifier)
10. 1,017.117 13,327.338 ↓ 235,425.0 235,425 1

Merge Join (cost=1.11..4,800,192.52 rows=1 width=95) (actual time=0.173..13,327.338 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
11. 769.800 6,623.470 ↓ 21.3 1,009,321 1

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

12. 5,853.670 5,853.670 ↑ 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.023..5,853.670 rows=5,801,870 loops=1)

13. 96.015 5,686.751 ↓ 27.9 602,477 1

Materialize (cost=0.56..3,208,985.58 rows=21,600 width=48) (actual time=0.020..5,686.751 rows=602,477 loops=1)

14. 80.140 5,590.736 ↓ 27.9 602,477 1

Subquery Scan on kpi_post (cost=0.56..3,208,931.58 rows=21,600 width=48) (actual time=0.018..5,590.736 rows=602,477 loops=1)

  • Filter: (kpi_post.line_device_capability IS NOT NULL)
  • Rows Removed by Filter: 54348
15. 586.142 5,510.596 ↓ 30.3 656,825 1

Unique (cost=0.56..3,208,714.49 rows=21,709 width=1,416) (actual time=0.017..5,510.596 rows=656,825 loops=1)

16. 4,924.454 4,924.454 ↑ 1.0 4,569,784 1

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,416) (actual time=0.015..4,924.454 rows=4,569,784 loops=1)

17. 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
18. 1,412.550 1,412.550 ↑ 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.006 rows=1 loops=235,425)

  • Index Cond: (line_identifier = l_post.line_identifier)
  • Heap Fetches: 235425
19. 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)
20. 1,458.055 3,060.525 ↑ 1.0 1 235,425

Index Scan using addresses_hashed_msisdn_idx on addresses addresses_1 (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))
21.          

SubPlan (forIndex Scan)

22. 481.436 1,602.470 ↑ 112,147.9 9 1

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

  • Group Key: addresses.hashed_msisdn
  • Filter: (count(1) > 1)
  • Rows Removed by Filter: 1009313
23. 1,121.034 1,121.034 ↑ 1.0 1,009,331 1

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

  • Heap Fetches: 1009331
24.          

CTE grouped_recurrents_post

25. 374.958 6,111.110 ↓ 80.0 480 1

GroupAggregate (cost=20,000,565,159.01..20,000,565,179.54 rows=6 width=308) (actual time=5,736.033..6,111.110 rows=480 loops=1)

  • Group Key: comparable_lines.line_identifier, comparable_lines.post_line_device_capability, comparable_lines.post_user_profile, comparable_lines.post_line_total_volume, comparable_lines.post_week_count, kpi_p80_post_agg.group_nr
26. 1.702 5,736.152 ↓ 2.1 579 1

Sort (cost=20,000,565,159.01..20,000,565,159.69 rows=272 width=1,769) (actual time=5,735.779..5,736.152 rows=579 loops=1)

  • Sort Key: comparable_lines.line_identifier, comparable_lines.post_line_device_capability, comparable_lines.post_user_profile, comparable_lines.post_line_total_volume, comparable_lines.post_week_count, kpi_p80_post_agg.group_nr
  • Sort Method: quicksort Memory: 1118kB
27. 189.931 5,734.450 ↓ 2.1 579 1

Hash Join (cost=20,000,218,544.41..20,000,565,148.01 rows=272 width=1,769) (actual time=5,184.521..5,734.450 rows=579 loops=1)

  • Hash Cond: ((kpi_p80_post.originating_msisdn = kpi_lc_post.originating_msisdn) AND (kpi_p80_post.p80 = kpi_lc_post.cluster_assignment))
28. 360.018 360.018 ↓ 1.0 1,687,055 1

Seq Scan on kpi_scen1_p80 kpi_p80_post (cost=10,000,000,000.00..10,000,334,018.61 rows=1,677,861 width=1,622) (actual time=0.005..360.018 rows=1,687,055 loops=1)

29. 0.159 5,184.501 ↓ 15.2 579 1

Hash (cost=10,000,218,543.84..10,000,218,543.84 rows=38 width=222) (actual time=5,184.501..5,184.501 rows=579 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 113kB
30. 0.609 5,184.342 ↓ 15.2 579 1

Merge Join (cost=10,000,205,892.05..10,000,218,543.84 rows=38 width=222) (actual time=5,183.530..5,184.342 rows=579 loops=1)

  • Merge Cond: ((kpi_lc_post.originating_msisdn = kpi_p80_post_agg.originating_msisdn) AND (kpi_lc_post.cluster_assignment = kpi_p80_post_agg.p80))
31. 8.950 1,303.796 ↓ 12.4 1,522 1

Sort (cost=466.93..467.24 rows=123 width=189) (actual time=1,303.676..1,303.796 rows=1,522 loops=1)

  • Sort Key: comparable_lines.line_identifier, kpi_lc_post.cluster_assignment
  • Sort Method: quicksort Memory: 389kB
32. 0.929 1,294.846 ↓ 12.4 1,522 1

Nested Loop (cost=0.43..462.66 rows=123 width=189) (actual time=0.018..1,294.846 rows=1,522 loops=1)

33. 0.117 0.117 ↓ 200.0 200 1

CTE Scan on comparable_lines (cost=0.00..0.02 rows=1 width=132) (actual time=0.001..0.117 rows=200 loops=1)

34. 1,293.800 1,293.800 ↑ 15.4 8 200

Index Scan using kpi_scen1_line_context_originating_msisdn_idx on kpi_scen1_line_context kpi_lc_post (cost=0.43..461.41 rows=123 width=57) (actual time=3.519..6.469 rows=8 loops=200)

  • Index Cond: (originating_msisdn = comparable_lines.line_identifier)
35. 3,499.857 3,879.937 ↑ 1,002.4 1,683 1

Sort (cost=10,000,205,423.85..10,000,209,641.52 rows=1,687,068 width=33) (actual time=3,879.841..3,879.937 rows=1,683 loops=1)

  • Sort Key: kpi_p80_post_agg.originating_msisdn, kpi_p80_post_agg.p80
  • Sort Method: quicksort Memory: 180955kB
36. 380.080 380.080 ↑ 1.0 1,687,068 1

Seq Scan on kpi_scen1_p80_agg kpi_p80_post_agg (cost=10,000,000,000.00..10,000,030,929.68 rows=1,687,068 width=33) (actual time=0.021..380.080 rows=1,687,068 loops=1)

37.          

CTE grouped_recurrents_pre

38. 421.732 157,847.339 ↓ 73.8 443 1

GroupAggregate (cost=10,001,955,282.28..10,001,955,299.37 rows=6 width=308) (actual time=157,425.819..157,847.339 rows=443 loops=1)

  • Group Key: comparable_lines_1.line_identifier, comparable_lines_1.pre_line_device_capability, comparable_lines_1.pre_user_profile, comparable_lines_1.pre_line_total_volume, comparable_lines_1.pre_week_count, kpi_p80_pre_agg.group_nr
39. 0.822 157,425.607 ↓ 2.4 549 1

Sort (cost=10,001,955,282.28..10,001,955,282.85 rows=226 width=2,010) (actual time=157,425.265..157,425.607 rows=549 loops=1)

  • Sort Key: comparable_lines_1.line_identifier, comparable_lines_1.pre_line_device_capability, comparable_lines_1.pre_user_profile, comparable_lines_1.pre_line_total_volume, comparable_lines_1.pre_week_count, kpi_p80_pre_agg.group_nr
  • Sort Method: quicksort Memory: 1234kB
40. 191.002 157,424.785 ↓ 2.4 549 1

Hash Join (cost=10,001,908,358.92..10,001,955,273.45 rows=226 width=2,010) (actual time=157,109.323..157,424.785 rows=549 loops=1)

  • Hash Cond: ((kpi_p80_pre_agg.originating_msisdn = kpi_lc_pre.originating_msisdn) AND (kpi_p80_pre_agg.p80 = kpi_p80_pre.p80))
41. 124.486 124.486 ↑ 1.0 1,815,958 1

Seq Scan on kpi_scen1_p80_agg kpi_p80_pre_agg (cost=10,000,000,000.00..10,000,033,292.58 rows=1,815,958 width=33) (actual time=0.010..124.486 rows=1,815,958 loops=1)

42. 1.891 157,109.297 ↓ 1.9 549 1

Hash (cost=1,908,354.56..1,908,354.56 rows=291 width=2,052) (actual time=157,109.297..157,109.297 rows=549 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 886kB
43. 2.607 157,107.406 ↓ 1.9 549 1

Nested Loop (cost=0.89..1,908,354.56 rows=291 width=2,052) (actual time=21,666.380..157,107.406 rows=549 loops=1)

  • Join Filter: (comparable_lines_1.line_identifier = kpi_lc_pre.originating_msisdn)
44. 171.578 22,982.274 ↓ 14.9 565 1

Hash Join (cost=0.46..1,560,602.08 rows=38 width=1,995) (actual time=21,666.361..22,982.274 rows=565 loops=1)

  • Hash Cond: (kpi_p80_pre.originating_msisdn = comparable_lines_1.line_identifier)
45. 1,144.359 1,144.359 ↑ 1.0 1,815,785 1

Index Scan using kpi_scen1_p80_p80_idx on kpi_scen1_p80 kpi_p80_pre (cost=0.43..1,553,792.47 rows=1,815,785 width=1,863) (actual time=0.012..1,144.359 rows=1,815,785 loops=1)

46. 0.038 21,666.337 ↓ 200.0 200 1

Hash (cost=0.02..0.02 rows=1 width=132) (actual time=21,666.337..21,666.337 rows=200 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
47. 21,666.299 21,666.299 ↓ 200.0 200 1

CTE Scan on comparable_lines comparable_lines_1 (cost=0.00..0.02 rows=1 width=132) (actual time=21,666.189..21,666.299 rows=200 loops=1)

48. 134,122.525 134,122.525 ↑ 10.0 1 565

Index Scan using kpi_scen1_line_context_cluster_assignment_idx on kpi_scen1_line_context kpi_lc_pre (cost=0.43..9,151.26 rows=10 width=57) (actual time=92.577..237.385 rows=1 loops=565)

  • Index Cond: (cluster_assignment = kpi_p80_pre.p80)
  • Filter: (kpi_p80_pre.originating_msisdn = originating_msisdn)
  • Rows Removed by Filter: 340507
49.          

CTE recurrents_combi

50. 350.744 164,311.570 ↓ 107.5 645 1

Hash Full Join (cost=0.20..2.56 rows=6 width=328) (actual time=163,584.885..164,311.570 rows=645 loops=1)

  • Hash Cond: (post.line_identifier = pre.line_identifier)
  • Join Filter: ((post.group_geom && pre.group_geom) AND _st_intersects(post.group_geom, pre.group_geom))
  • Rows Removed by Join Filter: 1161
51. 6,112.009 6,112.009 ↓ 80.0 480 1

CTE Scan on grouped_recurrents_post post (cost=0.00..0.12 rows=6 width=232) (actual time=5,736.037..6,112.009 rows=480 loops=1)

52. 0.502 157,848.817 ↓ 73.8 443 1

Hash (cost=0.12..0.12 rows=6 width=232) (actual time=157,848.817..157,848.817 rows=443 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 970kB
53. 157,848.315 157,848.315 ↓ 73.8 443 1

CTE Scan on grouped_recurrents_pre pre (cost=0.00..0.12 rows=6 width=232) (actual time=157,425.822..157,848.315 rows=443 loops=1)

54.          

CTE recurrents_combi_extra

55. 1.009 164,315.256 ↓ 107.5 645 1

WindowAgg (cost=0.20..0.41 rows=6 width=344) (actual time=164,313.972..164,315.256 rows=645 loops=1)

56. 1.446 164,314.247 ↓ 107.5 645 1

Sort (cost=0.20..0.21 rows=6 width=328) (actual time=164,313.951..164,314.247 rows=645 loops=1)

  • Sort Key: recurrents_combi.line_identifier
  • Sort Method: quicksort Memory: 2868kB
57. 164,312.801 164,312.801 ↓ 107.5 645 1

CTE Scan on recurrents_combi (cost=0.00..0.12 rows=6 width=328) (actual time=163,584.888..164,312.801 rows=645 loops=1)

58.          

CTE post_othercontexts_unnested

59. 640.984 644.704 ↓ 10.1 67,810 1

Nested Loop (cost=0.43..496.05 rows=6,700 width=1,133) (actual time=0.033..644.704 rows=67,810 loops=1)

60. 0.120 0.120 ↓ 200.0 200 1

CTE Scan on comparable_lines comparable_lines_2 (cost=0.00..0.02 rows=1 width=232) (actual time=0.000..0.120 rows=200 loops=1)

61. 3.600 3.600 ↑ 13.4 5 200

Index Scan using kpi_scen1_line_context_originating_msisdn_idx on kpi_scen1_line_context kpi_lc_post_1 (cost=0.43..462.03 rows=67 width=869) (actual time=0.011..0.018 rows=5 loops=200)

  • Index Cond: (originating_msisdn = comparable_lines_2.line_identifier)
  • Filter: ((cluster_assignment < 1) OR (cluster_assignment > 6))
  • Rows Removed by Filter: 3
62.          

CTE post_othercontexts_grouped

63. 14,395.304 16,214.353 ↓ 1.2 798 1

HashAggregate (cost=13,904.76..13,913.14 rows=670 width=136) (actual time=4,802.566..16,214.353 rows=798 loops=1)

  • Group Key: p.line_identifier, p.context, p.importance_mb, p.total_volume, p.total_volume_4g, p.total_volume_3g, p.total_volume_2g
64. 61.189 1,819.049 ↓ 25.8 67,392 1

Nested Loop (cost=11,290.36..13,845.95 rows=2,614 width=136) (actual time=31.780..1,819.049 rows=67,392 loops=1)

65. 236.256 1,083.940 ↓ 25.8 67,392 1

Hash Join (cost=11,289.95..11,483.58 rows=2,614 width=235) (actual time=31.764..1,083.940 rows=67,392 loops=1)

  • Hash Cond: (replace(p.ci, '$'::text, '|'::text) = c.cell_id_unique)
66. 815.985 815.985 ↓ 10.1 67,810 1

CTE Scan on post_othercontexts_unnested p (cost=0.00..134.00 rows=6,700 width=136) (actual time=0.038..815.985 rows=67,810 loops=1)

67. 6.580 31.699 ↓ 1.9 34,947 1

Hash (cost=11,063.09..11,063.09 rows=18,149 width=99) (actual time=31.699..31.699 rows=34,947 loops=1)

  • Buckets: 65536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 4939kB
68. 12.604 25.119 ↓ 1.9 34,947 1

Merge Join (cost=0.57..11,063.09 rows=18,149 width=99) (actual time=0.018..25.119 rows=34,947 loops=1)

  • Merge Cond: (s.site = c.site)
69. 0.657 0.657 ↑ 1.0 3,265 1

Index Scan using base_sites_site_idx on base_sites s (cost=0.28..151.25 rows=3,265 width=39) (actual time=0.007..0.657 rows=3,265 loops=1)

70. 11.858 11.858 ↑ 1.0 46,527 1

Index Scan using base_cells_removedupes_site_idx on base_cells_removedupes c (cost=0.29..10,605.86 rows=46,527 width=74) (actual time=0.007..11.858 rows=46,527 loops=1)

71. 673.920 673.920 ↑ 1.0 1 67,392

Index Only Scan using cell_areas_combi_cell_id_idx on cell_areas ca (cost=0.41..0.89 rows=1 width=67) (actual time=0.010..0.010 rows=1 loops=67,392)

  • Index Cond: (cell_id = c.cell_id_unique)
  • Heap Fetches: 67392
72.          

CTE pre_othercontexts_unnested

73. 684.192 2,238.173 ↓ 8.2 57,708 1

Hash Join (cost=70,358.96..669,523.62 rows=7,000 width=1,019) (actual time=616.164..2,238.173 rows=57,708 loops=1)

  • Hash Cond: (kpi_lc_pre_1.originating_msisdn = comparable_lines_3.line_identifier)
74. 1,064.606 1,553.942 ↓ 1.2 3,735,313 1

Bitmap Heap Scan on kpi_scen1_line_context kpi_lc_pre_1 (cost=70,358.92..657,459.42 rows=3,207,638 width=755) (actual time=616.103..1,553.942 rows=3,735,313 loops=1)

  • Recheck Cond: ((cluster_assignment < 1) OR (cluster_assignment > 6))
  • Heap Blocks: exact=530082
75. 0.002 489.336 ↓ 0.0 0 1

BitmapOr (cost=70,358.92..70,358.92 rows=3,722,433 width=0) (actual time=489.336..489.336 rows=0 loops=1)

76. 337.853 337.853 ↓ 1.0 2,683,609 1

Bitmap Index Scan on kpi_scen1_line_context_cluster_assignment_idx (cost=0.00..49,130.27 rows=2,659,978 width=0) (actual time=337.853..337.853 rows=2,683,609 loops=1)

  • Index Cond: (cluster_assignment < 1)
77. 151.481 151.481 ↑ 1.0 1,051,704 1

Bitmap Index Scan on kpi_scen1_line_context_cluster_assignment_idx (cost=0.00..19,624.84 rows=1,062,454 width=0) (actual time=151.481..151.481 rows=1,051,704 loops=1)

  • Index Cond: (cluster_assignment > 6)
78. 0.021 0.039 ↓ 200.0 200 1

Hash (cost=0.02..0.02 rows=1 width=232) (actual time=0.039..0.039 rows=200 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
79. 0.018 0.018 ↓ 200.0 200 1

CTE Scan on comparable_lines comparable_lines_3 (cost=0.00..0.02 rows=1 width=232) (actual time=0.001..0.018 rows=200 loops=1)

80.          

CTE pre_othercontexts_grouped

81. 10,977.716 14,253.977 ↓ 1.1 782 1

GroupAggregate (cost=10,000,020,757.94..10,000,020,941.64 rows=700 width=136) (actual time=3,261.934..14,253.977 rows=782 loops=1)

  • Group Key: p_1.line_identifier, p_1.context, p_1.importance_mb, p_1.total_volume, p_1.total_volume_4g, p_1.total_volume_3g, p_1.total_volume_2g
82. 116.147 3,276.261 ↓ 8.2 57,260 1

Sort (cost=10,000,020,757.94..10,000,020,775.44 rows=6,998 width=136) (actual time=3,256.944..3,276.261 rows=57,260 loops=1)

  • Sort Key: p_1.line_identifier, p_1.context, p_1.importance_mb, p_1.total_volume, p_1.total_volume_4g, p_1.total_volume_3g, p_1.total_volume_2g
  • Sort Method: quicksort Memory: 10724kB
83. 14.716 3,160.114 ↓ 8.2 57,260 1

Hash Join (cost=10,000,000,716.96..10,000,020,311.03 rows=6,998 width=136) (actual time=2,671.840..3,160.114 rows=57,260 loops=1)

  • Hash Cond: (c_1.site = s_1.site)
84. 0.000 3,144.592 ↓ 8.2 57,270 1

Nested Loop (cost=587.89..20,085.73 rows=6,998 width=111) (actual time=2,671.020..3,144.592 rows=57,270 loops=1)

85. 56.745 2,756.175 ↓ 8.2 57,270 1

Merge Join (cost=587.47..13,309.22 rows=7,000 width=189) (actual time=2,671.000..2,756.175 rows=57,270 loops=1)

  • Merge Cond: ((replace(p_1.ci, '$'::text, '|'::text)) = c_1.cell_id_unique)
86. 219.801 2,682.856 ↓ 8.2 57,271 1

Sort (cost=587.06..604.56 rows=7,000 width=136) (actual time=2,670.977..2,682.856 rows=57,271 loops=1)

  • Sort Key: (replace(p_1.ci, '$'::text, '|'::text))
  • Sort Method: quicksort Memory: 16810kB
87. 2,463.055 2,463.055 ↓ 8.2 57,708 1

CTE Scan on pre_othercontexts_unnested p_1 (cost=0.00..140.00 rows=7,000 width=136) (actual time=616.169..2,463.055 rows=57,708 loops=1)

88. 16.574 16.574 ↓ 2.1 81,530 1

Index Scan using base_cells_removedupes_cell_id_unique_idx on base_cells_removedupes c_1 (cost=0.41..12,402.78 rows=39,378 width=53) (actual time=0.012..16.574 rows=81,530 loops=1)

89. 400.890 400.890 ↑ 1.0 1 57,270

Index Only Scan using cell_areas_cell_id_idx on cell_areas ca_1 (cost=0.41..0.96 rows=1 width=40) (actual time=0.006..0.007 rows=1 loops=57,270)

  • Index Cond: (cell_id = c_1.cell_id_unique)
  • Heap Fetches: 57270
90. 0.392 0.806 ↑ 1.0 3,292 1

Hash (cost=10,000,000,087.92..10,000,000,087.92 rows=3,292 width=39) (actual time=0.806..0.806 rows=3,292 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 251kB
91. 0.414 0.414 ↑ 1.0 3,292 1

Seq Scan on base_sites s_1 (cost=10,000,000,000.00..10,000,000,087.92 rows=3,292 width=39) (actual time=0.006..0.414 rows=3,292 loops=1)

92.          

CTE othercontexts

93. 41,890.725 72,393.050 ↓ 211.2 845 1

Hash Right Join (cost=91.96..105.99 rows=4 width=272) (actual time=30,500.265..72,393.050 rows=845 loops=1)

  • Hash Cond: (COALESCE(post_1.line_identifier, pre_1.line_identifier) = cl.line_identifier)
94. 3.290 30,502.260 ↓ 1.2 845 1

Merge Full Join (cost=91.93..102.32 rows=700 width=240) (actual time=30,493.894..30,502.260 rows=845 loops=1)

  • Merge Cond: ((post_1.line_identifier = pre_1.line_identifier) AND (post_1.context = pre_1.context))
95. 7.631 16,230.140 ↓ 1.2 798 1

Sort (cost=44.85..46.52 rows=670 width=136) (actual time=16,227.756..16,230.140 rows=798 loops=1)

  • Sort Key: post_1.line_identifier, post_1.context
  • Sort Method: quicksort Memory: 15328kB
96. 16,222.509 16,222.509 ↓ 1.2 798 1

CTE Scan on post_othercontexts_grouped post_1 (cost=0.00..13.40 rows=670 width=136) (actual time=4,802.569..16,222.509 rows=798 loops=1)

97. 6.011 14,268.830 ↓ 1.1 782 1

Sort (cost=47.08..48.83 rows=700 width=136) (actual time=14,266.131..14,268.830 rows=782 loops=1)

  • Sort Key: pre_1.line_identifier, pre_1.context
  • Sort Method: quicksort Memory: 14202kB
98. 14,262.819 14,262.819 ↓ 1.1 782 1

CTE Scan on pre_othercontexts_grouped pre_1 (cost=0.00..14.00 rows=700 width=136) (actual time=3,261.937..14,262.819 rows=782 loops=1)

99. 0.041 0.065 ↓ 200.0 200 1

Hash (cost=0.02..0.02 rows=1 width=200) (actual time=0.065..0.065 rows=200 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
100. 0.024 0.024 ↓ 200.0 200 1

CTE Scan on comparable_lines cl (cost=0.00..0.02 rows=1 width=200) (actual time=0.001..0.024 rows=200 loops=1)

101.          

CTE contexts_2_3

102. 0.769 164,316.756 ↓ 107.5 645 1

Hash Left Join (cost=0.03..0.28 rows=6 width=416) (actual time=164,314.059..164,316.756 rows=645 loops=1)

  • Hash Cond: (rce.line_identifier = cl_1.line_identifier)
103. 164,315.916 164,315.916 ↓ 107.5 645 1

CTE Scan on recurrents_combi_extra rce (cost=0.00..0.12 rows=6 width=256) (actual time=164,313.974..164,315.916 rows=645 loops=1)

104. 0.036 0.071 ↓ 200.0 200 1

Hash (cost=0.02..0.02 rows=1 width=200) (actual time=0.071..0.071 rows=200 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
105. 0.035 0.035 ↓ 200.0 200 1

CTE Scan on comparable_lines cl_1 (cost=0.00..0.02 rows=1 width=200) (actual time=0.001..0.035 rows=200 loops=1)

106.          

CTE untraceable_pre

107. 1,112.218 5,246.968 ↓ 1.0 715,830 1

Merge Join (cost=1.11..3,196,629.17 rows=710,923 width=54) (actual time=0.037..5,246.968 rows=715,830 loops=1)

  • Merge Cond: (kpi_scen1_final_2_3.originating_msisdn = kpi_scen1_final_2_2.originating_msisdn)
108. 409.141 1,504.149 ↓ 21.3 1,009,321 1

Unique (cost=0.56..1,590,020.47 rows=47,398 width=21) (actual time=0.017..1,504.149 rows=1,009,321 loops=1)

109. 1,095.008 1,095.008 ↑ 1.0 5,801,870 1

Index Only Scan using kpi_scen1_final_2_originating_msisdn_idx on kpi_scen1_final_2 kpi_scen1_final_2_3 (cost=0.56..1,575,515.79 rows=5,801,871 width=21) (actual time=0.017..1,095.008 rows=5,801,870 loops=1)

  • Heap Fetches: 5801870
110. 2,630.601 2,630.601 ↓ 1.0 715,830 1

Index Scan using kpi_scen1_final_2_originating_msisdn_idx on kpi_scen1_final_2 kpi_scen1_final_2_2 (cost=0.56..1,590,020.47 rows=710,923 width=65) (actual time=0.012..2,630.601 rows=715,830 loops=1)

  • Filter: (context = '7_not traceable'::text)
  • Rows Removed by Filter: 5086041
111.          

CTE untraceable_post

112. 593.858 2,793.955 ↑ 1.0 466,376 1

Hash Join (cost=854,763.44..1,634,730.17 rows=468,589 width=54) (actual time=1,811.707..2,793.955 rows=466,376 loops=1)

  • Hash Cond: (kpi_scen1_final_2_4.originating_msisdn = kpi_scen1_final_2_5.originating_msisdn)
113. 475.272 866.602 ↑ 1.0 466,376 1

Bitmap Heap Scan on kpi_scen1_final_2 kpi_scen1_final_2_4 (cost=404,345.52..1,173,183.26 rows=468,589 width=67) (actual time=478.160..866.602 rows=466,376 loops=1)

  • Recheck Cond: (context = '7_not traceable'::text)
  • Heap Blocks: exact=378296
114. 391.330 391.330 ↑ 1.0 466,376 1

Bitmap Index Scan on kpi_scen1_final_2_originating_msisdn_context_idx (cost=0.00..404,228.38 rows=468,589 width=0) (actual time=391.330..391.330 rows=466,376 loops=1)

  • Index Cond: (context = '7_not traceable'::text)
115. 281.940 1,333.495 ↓ 30.3 656,825 1

Hash (cost=450,146.56..450,146.56 rows=21,709 width=21) (actual time=1,333.495..1,333.495 rows=656,825 loops=1)

  • Buckets: 1048576 (originally 32768) Batches: 1 (originally 1) Memory Usage: 42498kB
116. 302.459 1,051.555 ↓ 30.3 656,825 1

Unique (cost=0.56..449,929.47 rows=21,709 width=21) (actual time=0.012..1,051.555 rows=656,825 loops=1)

117. 749.096 749.096 ↑ 1.0 4,569,784 1

Index Only Scan using kpi_scen1_final_2_originating_msisdn_context_idx on kpi_scen1_final_2 kpi_scen1_final_2_5 (cost=0.56..438,504.19 rows=4,570,109 width=21) (actual time=0.012..749.096 rows=4,569,784 loops=1)

  • Heap Fetches: 0
118. 0.722 246,726.600 ↑ 55,894.2 1,490 1

Merge Left Join (cost=73,435.40..1,322,847.98 rows=83,282,323 width=288) (actual time=246,725.632..246,726.600 rows=1,490 loops=1)

  • Merge Cond: ("*SELECT* 1".line_identifier = untraceable_post.originating_msisdn)
119. 1.714 242,281.107 ↑ 23.9 1,490 1

Sort (cost=19,927.31..20,016.18 rows=35,546 width=280) (actual time=242,280.936..242,281.107 rows=1,490 loops=1)

  • Sort Key: "*SELECT* 1".line_identifier
  • Sort Method: quicksort Memory: 430kB
120. 101.719 242,279.393 ↑ 23.9 1,490 1

Hash Right Join (cost=0.61..17,240.50 rows=35,546 width=280) (actual time=236,714.258..242,279.393 rows=1,490 loops=1)

  • Hash Cond: (untraceable_pre.originating_msisdn = "*SELECT* 1".line_identifier)
121. 5,463.481 5,463.481 ↓ 1.0 715,830 1

CTE Scan on untraceable_pre (cost=0.00..14,218.46 rows=710,923 width=40) (actual time=0.039..5,463.481 rows=715,830 loops=1)

122. 0.967 236,714.193 ↓ 149.0 1,490 1

Hash (cost=0.49..0.49 rows=10 width=272) (actual time=236,714.193..236,714.193 rows=1,490 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 306kB
123. 0.290 236,713.226 ↓ 149.0 1,490 1

Append (cost=0.00..0.39 rows=10 width=272) (actual time=164,314.068..236,713.226 rows=1,490 loops=1)

124. 0.938 164,318.139 ↓ 107.5 645 1

Subquery Scan on *SELECT* 1 (cost=0.00..0.27 rows=6 width=272) (actual time=164,314.068..164,318.139 rows=645 loops=1)

125. 164,317.201 164,317.201 ↓ 107.5 645 1

CTE Scan on contexts_2_3 (cost=0.00..0.12 rows=6 width=416) (actual time=164,314.062..164,317.201 rows=645 loops=1)

126. 72,394.797 72,394.797 ↓ 211.2 845 1

CTE Scan on othercontexts (cost=0.00..0.08 rows=4 width=272) (actual time=30,500.268..72,394.797 rows=845 loops=1)

127. 1,507.008 4,444.771 ↑ 303.7 1,543 1

Sort (cost=53,508.09..54,679.57 rows=468,589 width=40) (actual time=4,444.690..4,444.771 rows=1,543 loops=1)

  • Sort Key: untraceable_post.originating_msisdn
  • Sort Method: quicksort Memory: 48724kB
128. 2,937.763 2,937.763 ↑ 1.0 466,376 1

CTE Scan on untraceable_post (cost=0.00..9,371.78 rows=468,589 width=40) (actual time=1,811.709..2,937.763 rows=466,376 loops=1)