explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Nvlz

Settings
# exclusive inclusive rows x rows loops node
1. 6.738 245,370.236 ↑ 55,890.1 1,490 1

Sort (cost=40,035,250,852.96..40,035,459,043.66 rows=83,276,280 width=288) (actual time=245,369.949..245,370.236 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.015 23,294.315 ↓ 200.0 200 1

Limit (cost=4,094,260.72..4,094,260.74 rows=1 width=74) (actual time=23,294.248..23,294.315 rows=200 loops=1)

4. 0.040 23,294.300 ↓ 200.0 200 1

Unique (cost=4,094,260.72..4,094,260.74 rows=1 width=74) (actual time=23,294.246..23,294.300 rows=200 loops=1)

5. 139.948 23,294.260 ↓ 200.0 200 1

Sort (cost=4,094,260.72..4,094,260.72 rows=1 width=74) (actual time=23,294.245..23,294.260 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. 681.622 23,154.312 ↓ 200,952.0 200,952 1

Nested Loop (cost=181,924.06..4,094,260.71 rows=1 width=74) (actual time=1,570.440..23,154.312 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. 126.464 19,176.740 ↓ 235,425.0 235,425 1

Nested Loop (cost=2.27..3,912,338.44 rows=1 width=190) (actual time=0.291..19,176.740 rows=235,425 loops=1)

  • Join Filter: (l_post.line_identifier = a_post.hashed_msisdn)
8. 161.770 17,402.301 ↓ 235,425.0 235,425 1

Nested Loop (cost=1.84..3,912,337.62 rows=1 width=137) (actual time=0.269..17,402.301 rows=235,425 loops=1)

9. 145.353 15,827.981 ↓ 235,425.0 235,425 1

Nested Loop (cost=1.42..3,912,337.10 rows=1 width=116) (actual time=0.248..15,827.981 rows=235,425 loops=1)

  • Join Filter: (kpi_post.originating_msisdn = l_post.line_identifier)
10. 1,113.825 14,270.078 ↓ 235,425.0 235,425 1

Merge Join (cost=0.99..3,912,336.39 rows=1 width=95) (actual time=0.220..14,270.078 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. 845.425 7,113.582 ↓ 21.3 1,009,321 1

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

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

13. 107.104 6,042.671 ↓ 27.9 602,477 1

Materialize (cost=0.43..2,321,129.45 rows=21,600 width=48) (actual time=0.059..6,042.671 rows=602,477 loops=1)

14. 92.017 5,935.567 ↓ 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.057..5,935.567 rows=602,477 loops=1)

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

Unique (cost=0.43..2,320,858.36 rows=21,709 width=1,416) (actual time=0.055..5,843.550 rows=656,825 loops=1)

16. 5,198.697 5,198.697 ↑ 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.053..5,198.697 rows=4,569,784 loops=1)

17. 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_post (cost=0.42..0.70 rows=1 width=21) (actual time=0.006..0.006 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.006..0.006 rows=1 loops=235,425)

  • Index Cond: (line_identifier = l_post.line_identifier)
  • Heap Fetches: 235425
19. 1,647.975 1,647.975 ↑ 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.007 rows=1 loops=235,425)

  • Index Cond: (hashed_msisdn = l_pre.line_identifier)
20. 1,727.771 3,295.950 ↑ 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.014..0.014 rows=1 loops=235,425)

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

SubPlan (forIndex Scan)

22. 448.062 1,568.179 ↑ 112,147.9 9 1

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

  • Group Key: addresses.hashed_msisdn
  • Filter: (count(1) > 1)
  • Rows Removed by Filter: 1009313
23. 1,120.117 1,120.117 ↑ 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,120.117 rows=1,009,331 loops=1)

  • Heap Fetches: 1009331
24.          

CTE grouped_recurrents_post

25. 367.935 3,711.034 ↓ 80.0 480 1

GroupAggregate (cost=20,000,565,159.01..20,000,565,179.54 rows=6 width=308) (actual time=3,343.012..3,711.034 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.636 3,343.099 ↓ 2.1 579 1

Sort (cost=20,000,565,159.01..20,000,565,159.69 rows=272 width=1,769) (actual time=3,342.760..3,343.099 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. 186.517 3,341.463 ↓ 2.1 579 1

Hash Join (cost=20,000,218,544.41..20,000,565,148.01 rows=272 width=1,769) (actual time=2,798.796..3,341.463 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. 356.171 356.171 ↓ 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.008..356.171 rows=1,687,055 loops=1)

29. 0.160 2,798.775 ↓ 15.2 579 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 113kB
30. 0.568 2,798.615 ↓ 15.2 579 1

Merge Join (cost=10,000,205,892.05..10,000,218,543.84 rows=38 width=222) (actual time=2,797.830..2,798.615 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. 1.764 3.703 ↓ 12.4 1,522 1

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

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

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

33. 0.032 0.032 ↓ 200.0 200 1

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

34. 1.600 1.600 ↑ 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=0.006..0.008 rows=8 loops=200)

  • Index Cond: (originating_msisdn = comparable_lines.line_identifier)
35. 2,630.587 2,794.344 ↑ 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=2,794.251..2,794.344 rows=1,683 loops=1)

  • Sort Key: kpi_p80_post_agg.originating_msisdn, kpi_p80_post_agg.p80
  • Sort Method: quicksort Memory: 180955kB
36. 163.757 163.757 ↑ 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.006..163.757 rows=1,687,068 loops=1)

37.          

CTE grouped_recurrents_pre

38. 414.281 161,447.624 ↓ 73.8 443 1

GroupAggregate (cost=10,001,955,282.28..10,001,955,299.37 rows=6 width=308) (actual time=161,033.526..161,447.624 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.852 161,033.343 ↓ 2.4 549 1

Sort (cost=10,001,955,282.28..10,001,955,282.85 rows=226 width=2,010) (actual time=161,032.996..161,033.343 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.452 161,032.491 ↓ 2.4 549 1

Hash Join (cost=10,001,908,358.92..10,001,955,273.45 rows=226 width=2,010) (actual time=160,715.449..161,032.491 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. 125.612 125.612 ↑ 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.009..125.612 rows=1,815,958 loops=1)

42. 1.998 160,715.427 ↓ 1.9 549 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 886kB
43. 2.520 160,713.429 ↓ 1.9 549 1

Nested Loop (cost=0.89..1,908,354.56 rows=291 width=2,052) (actual time=23,294.439..160,713.429 rows=549 loops=1)

  • Join Filter: (comparable_lines_1.line_identifier = kpi_lc_pre.originating_msisdn)
44. 174.097 24,630.659 ↓ 14.9 565 1

Hash Join (cost=0.46..1,560,602.08 rows=38 width=1,995) (actual time=23,294.420..24,630.659 rows=565 loops=1)

  • Hash Cond: (kpi_p80_pre.originating_msisdn = comparable_lines_1.line_identifier)
45. 1,162.166 1,162.166 ↑ 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,162.166 rows=1,815,785 loops=1)

46. 0.041 23,294.396 ↓ 200.0 200 1

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

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

CTE Scan on comparable_lines comparable_lines_1 (cost=0.00..0.02 rows=1 width=132) (actual time=23,294.250..23,294.355 rows=200 loops=1)

48. 136,080.250 136,080.250 ↑ 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=93.772..240.850 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. 343.678 165,504.659 ↓ 107.5 645 1

Hash Full Join (cost=0.20..2.56 rows=6 width=328) (actual time=164,792.132..165,504.659 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. 3,711.894 3,711.894 ↓ 80.0 480 1

CTE Scan on grouped_recurrents_post post (cost=0.00..0.12 rows=6 width=232) (actual time=3,343.015..3,711.894 rows=480 loops=1)

52. 0.519 161,449.087 ↓ 73.8 443 1

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

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

CTE Scan on grouped_recurrents_pre pre (cost=0.00..0.12 rows=6 width=232) (actual time=161,033.528..161,448.568 rows=443 loops=1)

54.          

CTE recurrents_combi_extra

55. 1.245 165,508.553 ↓ 107.5 645 1

WindowAgg (cost=0.20..0.41 rows=6 width=344) (actual time=165,507.062..165,508.553 rows=645 loops=1)

56. 1.419 165,507.308 ↓ 107.5 645 1

Sort (cost=0.20..0.21 rows=6 width=328) (actual time=165,507.040..165,507.308 rows=645 loops=1)

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

CTE Scan on recurrents_combi (cost=0.00..0.12 rows=6 width=328) (actual time=164,792.134..165,505.889 rows=645 loops=1)

58.          

CTE post_othercontexts_unnested

59. 45.633 48.699 ↓ 10.1 67,810 1

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

60. 0.066 0.066 ↓ 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.066 rows=200 loops=1)

61. 3.000 3.000 ↑ 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.009..0.015 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. 13,854.630 14,910.504 ↓ 1.2 798 1

HashAggregate (cost=13,904.76..13,913.14 rows=670 width=136) (actual time=3,767.751..14,910.504 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. 82.153 1,055.874 ↓ 25.8 67,392 1

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

65. 211.019 434.585 ↓ 25.8 67,392 1

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

  • Hash Cond: (replace(p.ci, '$'::text, '|'::text) = c.cell_id_unique)
66. 191.697 191.697 ↓ 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.035..191.697 rows=67,810 loops=1)

67. 6.713 31.869 ↓ 1.9 34,947 1

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

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

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

  • Merge Cond: (s.site = c.site)
69. 0.699 0.699 ↑ 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.008..0.699 rows=3,265 loops=1)

70. 11.779 11.779 ↑ 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.779 rows=46,527 loops=1)

71. 539.136 539.136 ↑ 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.008..0.008 rows=1 loops=67,392)

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

CTE pre_othercontexts_unnested

73. 333.008 1,843.709 ↓ 8.2 57,708 1

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

  • Hash Cond: (kpi_lc_pre_1.originating_msisdn = comparable_lines_3.line_identifier)
74. 1,016.364 1,510.663 ↓ 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=622.550..1,510.663 rows=3,735,313 loops=1)

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

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

76. 340.695 340.695 ↓ 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=340.695..340.695 rows=2,683,609 loops=1)

  • Index Cond: (cluster_assignment < 1)
77. 153.602 153.602 ↑ 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=153.602..153.602 rows=1,051,704 loops=1)

  • Index Cond: (cluster_assignment > 6)
78. 0.013 0.038 ↓ 200.0 200 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
79. 0.025 0.025 ↓ 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.025 rows=200 loops=1)

80.          

CTE pre_othercontexts_grouped

81. 10,856.338 13,708.057 ↓ 1.1 782 1

GroupAggregate (cost=10,000,020,757.94..10,000,020,941.64 rows=700 width=136) (actual time=2,836.916..13,708.057 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. 108.429 2,851.719 ↓ 8.2 57,260 1

Sort (cost=10,000,020,757.94..10,000,020,775.44 rows=6,998 width=136) (actual time=2,832.495..2,851.719 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. 13.928 2,743.290 ↓ 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,324.421..2,743.290 rows=57,260 loops=1)

  • Hash Cond: (c_1.site = s_1.site)
84. 41.768 2,728.619 ↓ 8.2 57,270 1

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

85. 51.180 2,400.501 ↓ 8.2 57,270 1

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

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

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

  • Sort Key: (replace(p_1.ci, '$'::text, '|'::text))
  • Sort Method: quicksort Memory: 16810kB
87. 2,131.072 2,131.072 ↓ 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=622.615..2,131.072 rows=57,708 loops=1)

88. 15.393 15.393 ↓ 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.011..15.393 rows=81,530 loops=1)

89. 286.350 286.350 ↑ 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.005..0.005 rows=1 loops=57,270)

  • Index Cond: (cell_id = c_1.cell_id_unique)
  • Heap Fetches: 57270
90. 0.375 0.743 ↑ 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.743..0.743 rows=3,292 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 251kB
91. 0.368 0.368 ↑ 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.368 rows=3,292 loops=1)

92.          

CTE othercontexts

93. 41,435.949 70,087.998 ↓ 211.2 845 1

Hash Right Join (cost=91.96..105.99 rows=4 width=272) (actual time=28,650.532..70,087.998 rows=845 loops=1)

  • Hash Cond: (COALESCE(post_1.line_identifier, pre_1.line_identifier) = cl.line_identifier)
94. 3.006 28,651.981 ↓ 1.2 845 1

Merge Full Join (cost=91.93..102.32 rows=700 width=240) (actual time=28,644.226..28,651.981 rows=845 loops=1)

  • Merge Cond: ((post_1.line_identifier = pre_1.line_identifier) AND (post_1.context = pre_1.context))
95. 7.308 14,925.912 ↓ 1.2 798 1

Sort (cost=44.85..46.52 rows=670 width=136) (actual time=14,923.647..14,925.912 rows=798 loops=1)

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

CTE Scan on post_othercontexts_grouped post_1 (cost=0.00..13.40 rows=670 width=136) (actual time=3,767.755..14,918.604 rows=798 loops=1)

97. 5.817 13,723.063 ↓ 1.1 782 1

Sort (cost=47.08..48.83 rows=700 width=136) (actual time=13,720.572..13,723.063 rows=782 loops=1)

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

CTE Scan on pre_othercontexts_grouped pre_1 (cost=0.00..14.00 rows=700 width=136) (actual time=2,836.920..13,717.246 rows=782 loops=1)

99. 0.035 0.068 ↓ 200.0 200 1

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

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

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

101.          

CTE contexts_2_3

102. 0.762 165,509.963 ↓ 107.5 645 1

Hash Left Join (cost=0.03..0.28 rows=6 width=416) (actual time=165,507.153..165,509.963 rows=645 loops=1)

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

CTE Scan on recurrents_combi_extra rce (cost=0.00..0.12 rows=6 width=256) (actual time=165,507.065..165,509.127 rows=645 loops=1)

104. 0.034 0.074 ↓ 200.0 200 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
105. 0.040 0.040 ↓ 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.040 rows=200 loops=1)

106.          

CTE untraceable_pre

107. 1,091.645 5,159.110 ↓ 1.0 715,830 1

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

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

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

109. 1,066.373 1,066.373 ↑ 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,066.373 rows=5,801,870 loops=1)

  • Heap Fetches: 5801870
110. 2,593.092 2,593.092 ↓ 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.011..2,593.092 rows=715,830 loops=1)

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

CTE untraceable_post

112. 589.541 2,480.444 ↑ 1.0 466,376 1

Hash Join (cost=575,431.18..1,355,372.73 rows=468,555 width=54) (actual time=1,506.655..2,480.444 rows=466,376 loops=1)

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

Bitmap Heap Scan on kpi_scen1_final_2 kpi_scen1_final_2_4 (cost=404,343.08..1,173,156.44 rows=468,555 width=67) (actual time=643.176..1,027.469 rows=466,376 loops=1)

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

Bitmap Index Scan on kpi_scen1_final_2_originating_msisdn_context_idx (cost=0.00..404,225.94 rows=468,555 width=0) (actual time=554.509..554.509 rows=466,376 loops=1)

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

Hash (cost=170,816.74..170,816.74 rows=21,709 width=21) (actual time=863.434..863.434 rows=656,825 loops=1)

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

Unique (cost=0.43..170,599.65 rows=21,709 width=21) (actual time=0.027..774.161 rows=656,825 loops=1)

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

Index Only Scan using kpi_scen1_final_2_originating_msisdn_idx on kpi_scen1_final_2 kpi_scen1_final_2_5 (cost=0.43..159,175.19 rows=4,569,784 width=21) (actual time=0.027..480.249 rows=4,569,784 loops=1)

  • Heap Fetches: 0
118. 2.057 245,363.498 ↑ 55,890.1 1,490 1

Merge Left Join (cost=73,431.28..1,322,753.21 rows=83,276,280 width=288) (actual time=245,360.849..245,363.498 rows=1,490 loops=1)

  • Merge Cond: ("*SELECT* 1".line_identifier = untraceable_post.originating_msisdn)
119. 1.904 241,066.622 ↑ 23.9 1,490 1

Sort (cost=19,927.31..20,016.18 rows=35,546 width=280) (actual time=241,066.295..241,066.622 rows=1,490 loops=1)

  • Sort Key: "*SELECT* 1".line_identifier
  • Sort Method: quicksort Memory: 430kB
120. 92.787 241,064.718 ↑ 23.9 1,490 1

Hash Right Join (cost=0.61..17,240.50 rows=35,546 width=280) (actual time=235,602.188..241,064.718 rows=1,490 loops=1)

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

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

122. 0.953 235,602.122 ↓ 149.0 1,490 1

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

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 306kB
123. 0.257 235,601.169 ↓ 149.0 1,490 1

Append (cost=0.00..0.39 rows=10 width=272) (actual time=165,507.164..235,601.169 rows=1,490 loops=1)

124. 0.950 165,511.293 ↓ 107.5 645 1

Subquery Scan on *SELECT* 1 (cost=0.00..0.27 rows=6 width=272) (actual time=165,507.162..165,511.293 rows=645 loops=1)

125. 165,510.343 165,510.343 ↓ 107.5 645 1

CTE Scan on contexts_2_3 (cost=0.00..0.12 rows=6 width=416) (actual time=165,507.156..165,510.343 rows=645 loops=1)

126. 70,089.619 70,089.619 ↓ 211.2 845 1

CTE Scan on othercontexts (cost=0.00..0.08 rows=4 width=272) (actual time=28,650.535..70,089.619 rows=845 loops=1)

127. 1,672.432 4,294.819 ↑ 303.7 1,543 1

Sort (cost=53,503.96..54,675.35 rows=468,555 width=40) (actual time=4,294.544..4,294.819 rows=1,543 loops=1)

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

CTE Scan on untraceable_post (cost=0.00..9,371.10 rows=468,555 width=40) (actual time=1,506.658..2,622.387 rows=466,376 loops=1)