#	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)

Sort  (cost=40036419804.89..40036628010.70 rows=83282323 width=288) (actual time=246728.708..246728.795 rows=1490 loops=1)
  Sort Key: "*SELECT* 1".line_identifier, "*SELECT* 1".context, "*SELECT* 1".post_import
  Sort Method: quicksort  Memory: 431kB
  CTE comparable_lines
    ->  Limit  (cost=4982116.84..4982116.87 rows=1 width=74) (actual time=21666.188..21666.257 rows=200 loops=1)
          ->  Unique  (cost=4982116.84..4982116.87 rows=1 width=74) (actual time=21666.187..21666.247 rows=200 loops=1)
                ->  Sort  (cost=4982116.84..4982116.85 rows=1 width=74) (actual time=21666.186..21666.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
                      ->  Nested Loop  (cost=181924.19..4982116.83 rows=1 width=74) (actual time=1604.677..21537.134 rows=200952 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
                            ->  Nested Loop  (cost=2.39..4800194.57 rows=1 width=190) (actual time=0.245..17758.963 rows=235425 loops=1)
                                  Join Filter: (l_post.line_identifier = a_post.hashed_msisdn)
                                  ->  Nested Loop  (cost=1.97..4800193.75 rows=1 width=137) (actual time=0.222..16143.945 rows=235425 loops=1)
                                        ->  Nested Loop  (cost=1.54..4800193.23 rows=1 width=116) (actual time=0.201..14719.235 rows=235425 loops=1)
                                              Join Filter: (kpi_post.originating_msisdn = l_post.line_identifier)
                                              ->  Merge Join  (cost=1.11..4800192.52 rows=1 width=95) (actual time=0.173..13327.338 rows=235425 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
                                                    ->  Unique  (cost=0.56..1590020.47 rows=47398 width=1383) (actual time=0.027..6623.470 rows=1009321 loops=1)
                                                          ->  Index Scan using kpi_scen1_final_2_originating_msisdn_idx on kpi_scen1_final_2  (cost=0.56..1575515.79 rows=5801871 width=1383) (actual time=0.023..5853.670 rows=5801870 loops=1)
                                                    ->  Materialize  (cost=0.56..3208985.58 rows=21600 width=48) (actual time=0.020..5686.751 rows=602477 loops=1)
                                                          ->  Subquery Scan on kpi_post  (cost=0.56..3208931.58 rows=21600 width=48) (actual time=0.018..5590.736 rows=602477 loops=1)
                                                                Filter: (kpi_post.line_device_capability IS NOT NULL)
                                                                Rows Removed by Filter: 54348
                                                                ->  Unique  (cost=0.56..3208714.49 rows=21709 width=1416) (actual time=0.017..5510.596 rows=656825 loops=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..3197289.22 rows=4570109 width=1416) (actual time=0.015..4924.454 rows=4569784 loops=1)
                                              ->  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=235425)
                                                    Index Cond: (line_identifier = kpi_scen1_final_2.originating_msisdn)
                                                    Heap Fetches: 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 time=0.005..0.006 rows=1 loops=235425)
                                              Index Cond: (line_identifier = l_post.line_identifier)
                                              Heap Fetches: 235425
                                  ->  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=235425)
                                        Index Cond: (hashed_msisdn = l_pre.line_identifier)
                            ->  Index Scan using addresses_hashed_msisdn_idx on addresses addresses_1  (cost=181921.80..181921.99 rows=1 width=53) (actual time=0.013..0.013 rows=1 loops=235425)
                                  Index Cond: (hashed_msisdn = l_pre.line_identifier)
                                  Filter: (NOT (hashed SubPlan 1))
                                  SubPlan 1
                                    ->  GroupAggregate  (cost=0.42..179398.05 rows=1009331 width=21) (actual time=18.838..1602.470 rows=9 loops=1)
                                          Group Key: addresses.hashed_msisdn
                                          Filter: (count(1) > 1)
                                          Rows Removed by Filter: 1009313
                                          ->  Index Only Scan using addresses_hashed_msisdn_idx on addresses  (cost=0.42..164258.08 rows=1009331 width=21) (actual time=0.017..1121.034 rows=1009331 loops=1)
                                                Heap Fetches: 1009331
  CTE grouped_recurrents_post
    ->  GroupAggregate  (cost=20000565159.01..20000565179.54 rows=6 width=308) (actual time=5736.033..6111.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
          ->  Sort  (cost=20000565159.01..20000565159.69 rows=272 width=1769) (actual time=5735.779..5736.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
                ->  Hash Join  (cost=20000218544.41..20000565148.01 rows=272 width=1769) (actual time=5184.521..5734.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))
                      ->  Seq Scan on kpi_scen1_p80 kpi_p80_post  (cost=10000000000.00..10000334018.61 rows=1677861 width=1622) (actual time=0.005..360.018 rows=1687055 loops=1)
                      ->  Hash  (cost=10000218543.84..10000218543.84 rows=38 width=222) (actual time=5184.501..5184.501 rows=579 loops=1)
                            Buckets: 1024  Batches: 1  Memory Usage: 113kB
                            ->  Merge Join  (cost=10000205892.05..10000218543.84 rows=38 width=222) (actual time=5183.530..5184.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))
                                  ->  Sort  (cost=466.93..467.24 rows=123 width=189) (actual time=1303.676..1303.796 rows=1522 loops=1)
                                        Sort Key: comparable_lines.line_identifier, kpi_lc_post.cluster_assignment
                                        Sort Method: quicksort  Memory: 389kB
                                        ->  Nested Loop  (cost=0.43..462.66 rows=123 width=189) (actual time=0.018..1294.846 rows=1522 loops=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)
                                              ->  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)
                                  ->  Sort  (cost=10000205423.85..10000209641.52 rows=1687068 width=33) (actual time=3879.841..3879.937 rows=1683 loops=1)
                                        Sort Key: kpi_p80_post_agg.originating_msisdn, kpi_p80_post_agg.p80
                                        Sort Method: quicksort  Memory: 180955kB
                                        ->  Seq Scan on kpi_scen1_p80_agg kpi_p80_post_agg  (cost=10000000000.00..10000030929.68 rows=1687068 width=33) (actual time=0.021..380.080 rows=1687068 loops=1)
  CTE grouped_recurrents_pre
    ->  GroupAggregate  (cost=10001955282.28..10001955299.37 rows=6 width=308) (actual time=157425.819..157847.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
          ->  Sort  (cost=10001955282.28..10001955282.85 rows=226 width=2010) (actual time=157425.265..157425.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
                ->  Hash Join  (cost=10001908358.92..10001955273.45 rows=226 width=2010) (actual time=157109.323..157424.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))
                      ->  Seq Scan on kpi_scen1_p80_agg kpi_p80_pre_agg  (cost=10000000000.00..10000033292.58 rows=1815958 width=33) (actual time=0.010..124.486 rows=1815958 loops=1)
                      ->  Hash  (cost=1908354.56..1908354.56 rows=291 width=2052) (actual time=157109.297..157109.297 rows=549 loops=1)
                            Buckets: 1024  Batches: 1  Memory Usage: 886kB
                            ->  Nested Loop  (cost=0.89..1908354.56 rows=291 width=2052) (actual time=21666.380..157107.406 rows=549 loops=1)
                                  Join Filter: (comparable_lines_1.line_identifier = kpi_lc_pre.originating_msisdn)
                                  ->  Hash Join  (cost=0.46..1560602.08 rows=38 width=1995) (actual time=21666.361..22982.274 rows=565 loops=1)
                                        Hash Cond: (kpi_p80_pre.originating_msisdn = comparable_lines_1.line_identifier)
                                        ->  Index Scan using kpi_scen1_p80_p80_idx on kpi_scen1_p80 kpi_p80_pre  (cost=0.43..1553792.47 rows=1815785 width=1863) (actual time=0.012..1144.359 rows=1815785 loops=1)
                                        ->  Hash  (cost=0.02..0.02 rows=1 width=132) (actual time=21666.337..21666.337 rows=200 loops=1)
                                              Buckets: 1024  Batches: 1  Memory Usage: 24kB
                                              ->  CTE Scan on comparable_lines comparable_lines_1  (cost=0.00..0.02 rows=1 width=132) (actual time=21666.189..21666.299 rows=200 loops=1)
                                  ->  Index Scan using kpi_scen1_line_context_cluster_assignment_idx on kpi_scen1_line_context kpi_lc_pre  (cost=0.43..9151.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
  CTE recurrents_combi
    ->  Hash Full Join  (cost=0.20..2.56 rows=6 width=328) (actual time=163584.885..164311.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
          ->  CTE Scan on grouped_recurrents_post post  (cost=0.00..0.12 rows=6 width=232) (actual time=5736.037..6112.009 rows=480 loops=1)
          ->  Hash  (cost=0.12..0.12 rows=6 width=232) (actual time=157848.817..157848.817 rows=443 loops=1)
                Buckets: 1024  Batches: 1  Memory Usage: 970kB
                ->  CTE Scan on grouped_recurrents_pre pre  (cost=0.00..0.12 rows=6 width=232) (actual time=157425.822..157848.315 rows=443 loops=1)
  CTE recurrents_combi_extra
    ->  WindowAgg  (cost=0.20..0.41 rows=6 width=344) (actual time=164313.972..164315.256 rows=645 loops=1)
          ->  Sort  (cost=0.20..0.21 rows=6 width=328) (actual time=164313.951..164314.247 rows=645 loops=1)
                Sort Key: recurrents_combi.line_identifier
                Sort Method: quicksort  Memory: 2868kB
                ->  CTE Scan on recurrents_combi  (cost=0.00..0.12 rows=6 width=328) (actual time=163584.888..164312.801 rows=645 loops=1)
  CTE post_othercontexts_unnested
    ->  Nested Loop  (cost=0.43..496.05 rows=6700 width=1133) (actual time=0.033..644.704 rows=67810 loops=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)
          ->  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
  CTE post_othercontexts_grouped
    ->  HashAggregate  (cost=13904.76..13913.14 rows=670 width=136) (actual time=4802.566..16214.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
          ->  Nested Loop  (cost=11290.36..13845.95 rows=2614 width=136) (actual time=31.780..1819.049 rows=67392 loops=1)
                ->  Hash Join  (cost=11289.95..11483.58 rows=2614 width=235) (actual time=31.764..1083.940 rows=67392 loops=1)
                      Hash Cond: (replace(p.ci, '$'::text, '|'::text) = c.cell_id_unique)
                      ->  CTE Scan on post_othercontexts_unnested p  (cost=0.00..134.00 rows=6700 width=136) (actual time=0.038..815.985 rows=67810 loops=1)
                      ->  Hash  (cost=11063.09..11063.09 rows=18149 width=99) (actual time=31.699..31.699 rows=34947 loops=1)
                            Buckets: 65536 (originally 32768)  Batches: 1 (originally 1)  Memory Usage: 4939kB
                            ->  Merge Join  (cost=0.57..11063.09 rows=18149 width=99) (actual time=0.018..25.119 rows=34947 loops=1)
                                  Merge Cond: (s.site = c.site)
                                  ->  Index Scan using base_sites_site_idx on base_sites s  (cost=0.28..151.25 rows=3265 width=39) (actual time=0.007..0.657 rows=3265 loops=1)
                                  ->  Index Scan using base_cells_removedupes_site_idx on base_cells_removedupes c  (cost=0.29..10605.86 rows=46527 width=74) (actual time=0.007..11.858 rows=46527 loops=1)
                ->  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=67392)
                      Index Cond: (cell_id = c.cell_id_unique)
                      Heap Fetches: 67392
  CTE pre_othercontexts_unnested
    ->  Hash Join  (cost=70358.96..669523.62 rows=7000 width=1019) (actual time=616.164..2238.173 rows=57708 loops=1)
          Hash Cond: (kpi_lc_pre_1.originating_msisdn = comparable_lines_3.line_identifier)
          ->  Bitmap Heap Scan on kpi_scen1_line_context kpi_lc_pre_1  (cost=70358.92..657459.42 rows=3207638 width=755) (actual time=616.103..1553.942 rows=3735313 loops=1)
                Recheck Cond: ((cluster_assignment < 1) OR (cluster_assignment > 6))
                Heap Blocks: exact=530082
                ->  BitmapOr  (cost=70358.92..70358.92 rows=3722433 width=0) (actual time=489.336..489.336 rows=0 loops=1)
                      ->  Bitmap Index Scan on kpi_scen1_line_context_cluster_assignment_idx  (cost=0.00..49130.27 rows=2659978 width=0) (actual time=337.853..337.853 rows=2683609 loops=1)
                            Index Cond: (cluster_assignment < 1)
                      ->  Bitmap Index Scan on kpi_scen1_line_context_cluster_assignment_idx  (cost=0.00..19624.84 rows=1062454 width=0) (actual time=151.481..151.481 rows=1051704 loops=1)
                            Index Cond: (cluster_assignment > 6)
          ->  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
                ->  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)
  CTE pre_othercontexts_grouped
    ->  GroupAggregate  (cost=10000020757.94..10000020941.64 rows=700 width=136) (actual time=3261.934..14253.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
          ->  Sort  (cost=10000020757.94..10000020775.44 rows=6998 width=136) (actual time=3256.944..3276.261 rows=57260 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
                ->  Hash Join  (cost=10000000716.96..10000020311.03 rows=6998 width=136) (actual time=2671.840..3160.114 rows=57260 loops=1)
                      Hash Cond: (c_1.site = s_1.site)
                      ->  Nested Loop  (cost=587.89..20085.73 rows=6998 width=111) (actual time=2671.020..3144.592 rows=57270 loops=1)
                            ->  Merge Join  (cost=587.47..13309.22 rows=7000 width=189) (actual time=2671.000..2756.175 rows=57270 loops=1)
                                  Merge Cond: ((replace(p_1.ci, '$'::text, '|'::text)) = c_1.cell_id_unique)
                                  ->  Sort  (cost=587.06..604.56 rows=7000 width=136) (actual time=2670.977..2682.856 rows=57271 loops=1)
                                        Sort Key: (replace(p_1.ci, '$'::text, '|'::text))
                                        Sort Method: quicksort  Memory: 16810kB
                                        ->  CTE Scan on pre_othercontexts_unnested p_1  (cost=0.00..140.00 rows=7000 width=136) (actual time=616.169..2463.055 rows=57708 loops=1)
                                  ->  Index Scan using base_cells_removedupes_cell_id_unique_idx on base_cells_removedupes c_1  (cost=0.41..12402.78 rows=39378 width=53) (actual time=0.012..16.574 rows=81530 loops=1)
                            ->  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=57270)
                                  Index Cond: (cell_id = c_1.cell_id_unique)
                                  Heap Fetches: 57270
                      ->  Hash  (cost=10000000087.92..10000000087.92 rows=3292 width=39) (actual time=0.806..0.806 rows=3292 loops=1)
                            Buckets: 4096  Batches: 1  Memory Usage: 251kB
                            ->  Seq Scan on base_sites s_1  (cost=10000000000.00..10000000087.92 rows=3292 width=39) (actual time=0.006..0.414 rows=3292 loops=1)
  CTE othercontexts
    ->  Hash Right Join  (cost=91.96..105.99 rows=4 width=272) (actual time=30500.265..72393.050 rows=845 loops=1)
          Hash Cond: (COALESCE(post_1.line_identifier, pre_1.line_identifier) = cl.line_identifier)
          ->  Merge Full Join  (cost=91.93..102.32 rows=700 width=240) (actual time=30493.894..30502.260 rows=845 loops=1)
                Merge Cond: ((post_1.line_identifier = pre_1.line_identifier) AND (post_1.context = pre_1.context))
                ->  Sort  (cost=44.85..46.52 rows=670 width=136) (actual time=16227.756..16230.140 rows=798 loops=1)
                      Sort Key: post_1.line_identifier, post_1.context
                      Sort Method: quicksort  Memory: 15328kB
                      ->  CTE Scan on post_othercontexts_grouped post_1  (cost=0.00..13.40 rows=670 width=136) (actual time=4802.569..16222.509 rows=798 loops=1)
                ->  Sort  (cost=47.08..48.83 rows=700 width=136) (actual time=14266.131..14268.830 rows=782 loops=1)
                      Sort Key: pre_1.line_identifier, pre_1.context
                      Sort Method: quicksort  Memory: 14202kB
                      ->  CTE Scan on pre_othercontexts_grouped pre_1  (cost=0.00..14.00 rows=700 width=136) (actual time=3261.937..14262.819 rows=782 loops=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
                ->  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)
  CTE contexts_2_3
    ->  Hash Left Join  (cost=0.03..0.28 rows=6 width=416) (actual time=164314.059..164316.756 rows=645 loops=1)
          Hash Cond: (rce.line_identifier = cl_1.line_identifier)
          ->  CTE Scan on recurrents_combi_extra rce  (cost=0.00..0.12 rows=6 width=256) (actual time=164313.974..164315.916 rows=645 loops=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
                ->  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)
  CTE untraceable_pre
    ->  Merge Join  (cost=1.11..3196629.17 rows=710923 width=54) (actual time=0.037..5246.968 rows=715830 loops=1)
          Merge Cond: (kpi_scen1_final_2_3.originating_msisdn = kpi_scen1_final_2_2.originating_msisdn)
          ->  Unique  (cost=0.56..1590020.47 rows=47398 width=21) (actual time=0.017..1504.149 rows=1009321 loops=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..1575515.79 rows=5801871 width=21) (actual time=0.017..1095.008 rows=5801870 loops=1)
                      Heap Fetches: 5801870
          ->  Index Scan using kpi_scen1_final_2_originating_msisdn_idx on kpi_scen1_final_2 kpi_scen1_final_2_2  (cost=0.56..1590020.47 rows=710923 width=65) (actual time=0.012..2630.601 rows=715830 loops=1)
                Filter: (context = '7_not traceable'::text)
                Rows Removed by Filter: 5086041
  CTE untraceable_post
    ->  Hash Join  (cost=854763.44..1634730.17 rows=468589 width=54) (actual time=1811.707..2793.955 rows=466376 loops=1)
          Hash Cond: (kpi_scen1_final_2_4.originating_msisdn = kpi_scen1_final_2_5.originating_msisdn)
          ->  Bitmap Heap Scan on kpi_scen1_final_2 kpi_scen1_final_2_4  (cost=404345.52..1173183.26 rows=468589 width=67) (actual time=478.160..866.602 rows=466376 loops=1)
                Recheck Cond: (context = '7_not traceable'::text)
                Heap Blocks: exact=378296
                ->  Bitmap Index Scan on kpi_scen1_final_2_originating_msisdn_context_idx  (cost=0.00..404228.38 rows=468589 width=0) (actual time=391.330..391.330 rows=466376 loops=1)
                      Index Cond: (context = '7_not traceable'::text)
          ->  Hash  (cost=450146.56..450146.56 rows=21709 width=21) (actual time=1333.495..1333.495 rows=656825 loops=1)
                Buckets: 1048576 (originally 32768)  Batches: 1 (originally 1)  Memory Usage: 42498kB
                ->  Unique  (cost=0.56..449929.47 rows=21709 width=21) (actual time=0.012..1051.555 rows=656825 loops=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..438504.19 rows=4570109 width=21) (actual time=0.012..749.096 rows=4569784 loops=1)
                            Heap Fetches: 0
  ->  Merge Left Join  (cost=73435.40..1322847.98 rows=83282323 width=288) (actual time=246725.632..246726.600 rows=1490 loops=1)
        Merge Cond: ("*SELECT* 1".line_identifier = untraceable_post.originating_msisdn)
        ->  Sort  (cost=19927.31..20016.18 rows=35546 width=280) (actual time=242280.936..242281.107 rows=1490 loops=1)
              Sort Key: "*SELECT* 1".line_identifier
              Sort Method: quicksort  Memory: 430kB
              ->  Hash Right Join  (cost=0.61..17240.50 rows=35546 width=280) (actual time=236714.258..242279.393 rows=1490 loops=1)
                    Hash Cond: (untraceable_pre.originating_msisdn = "*SELECT* 1".line_identifier)
                    ->  CTE Scan on untraceable_pre  (cost=0.00..14218.46 rows=710923 width=40) (actual time=0.039..5463.481 rows=715830 loops=1)
                    ->  Hash  (cost=0.49..0.49 rows=10 width=272) (actual time=236714.193..236714.193 rows=1490 loops=1)
                          Buckets: 2048 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 306kB
                          ->  Append  (cost=0.00..0.39 rows=10 width=272) (actual time=164314.068..236713.226 rows=1490 loops=1)
                                ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..0.27 rows=6 width=272) (actual time=164314.068..164318.139 rows=645 loops=1)
                                      ->  CTE Scan on contexts_2_3  (cost=0.00..0.12 rows=6 width=416) (actual time=164314.062..164317.201 rows=645 loops=1)
                                ->  CTE Scan on othercontexts  (cost=0.00..0.08 rows=4 width=272) (actual time=30500.268..72394.797 rows=845 loops=1)
        ->  Sort  (cost=53508.09..54679.57 rows=468589 width=40) (actual time=4444.690..4444.771 rows=1543 loops=1)
              Sort Key: untraceable_post.originating_msisdn
              Sort Method: quicksort  Memory: 48724kB
              ->  CTE Scan on untraceable_post  (cost=0.00..9371.78 rows=468589 width=40) (actual time=1811.709..2937.763 rows=466376 loops=1)
Planning time: 18.354 ms
Execution time: 246767.711 ms

Per node type stats

node type	count	sum of times	% of query
Append	1	0.290 ms	0.0 %
Bitmap Heap Scan	2	1,539.878 ms	0.6 %
Bitmap Index Scan	3	880.664 ms	0.4 %
BitmapOr	1	0.002 ms	0.0 %
CTE Scan	18	793,133.264 ms	321.5 %
GroupAggregate	4	12,255.842 ms	5.0 %
Hash	11	292.567 ms	0.1 %
Hash Full Join	1	350.744 ms	0.1 %
Hash Join	7	2,081.533 ms	0.8 %
Hash Left Join	1	0.769 ms	0.0 %
Hash Right Join	2	41,992.444 ms	17.0 %
HashAggregate	1	14,395.304 ms	5.8 %
Index Only Scan	7	6,629.623 ms	2.7 %
Index Scan	12	152,872.703 ms	62.0 %
Limit	1	0.010 ms	0.0 %
Materialize	1	96.015 ms	0.0 %
Merge Full Join	1	3.290 ms	0.0 %
Merge Join	5	2,199.293 ms	0.9 %
Merge Left Join	1	0.722 ms	0.0 %
Nested Loop	9	1,852.755 ms	0.8 %
Seq Scan	4	864.998 ms	0.4 %
Sort	13	5,502.351 ms	2.2 %
Subquery Scan	2	81.078 ms	0.0 %
Unique	5	2,067.588 ms	0.8 %
WindowAgg	1	1.009 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
addresses	3	3,991.639 ms	1.6 %
Index Only Scan	1	1,121.034 ms	28.1 %
Index Scan	2	2,870.605 ms	71.9 %
base_cells_removedupes	2	28.432 ms	0.0 %
Index Scan	2	28.432 ms	100.0 %
base_sites	2	1.071 ms	0.0 %
Index Scan	1	0.657 ms	61.3 %
Seq Scan	1	0.414 ms	38.7 %
cell_areas	2	1,074.810 ms	0.4 %
Index Only Scan	2	1,074.810 ms	100.0 %
kpi_scen1_final_2	6	15,728.101 ms	6.4 %
Bitmap Heap Scan	1	475.272 ms	3.0 %
Index Only Scan	2	1,844.104 ms	11.7 %
Index Scan	3	13,408.725 ms	85.3 %
kpi_scen1_line_context	4	136,484.531 ms	55.3 %
Bitmap Heap Scan	1	1,064.606 ms	0.8 %
Index Scan	3	135,419.925 ms	99.2 %
kpi_scen1_p80	2	1,504.377 ms	0.6 %
Index Scan	1	1,144.359 ms	76.1 %
Seq Scan	1	360.018 ms	23.9 %
kpi_scen1_p80_agg	2	504.566 ms	0.2 %
Seq Scan	2	504.566 ms	100.0 %
lines_snapshot_usage	2	2,589.675 ms	1.0 %
Index Only Scan	2	2,589.675 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DKqu

Color mode:

Visible columns:

Per node type stats

Per table stats