GroupAggregate (cost=356113.02..356115.54 rows=1 width=76) (actual time=1193.334..1194.411 rows=3 loops=1) Group Key: (1), (CASE WHEN ((fa.targeting_ios IS NOT NULL) AND (fa.targeting_android IS NOT NULL)) THEN 'iOS and Android'::text WHEN ((fa.targeting_ios IS NOT NULL) AND (fa.targeting_android IS NULL)) THEN 'iOS'::text WHEN ((fa.targeting_ios IS NULL) AND (fa.targeting_android IS NOT NULL)) THEN 'Android'::text WHEN ((fa.targeting_ios IS NOT NULL) AND(fa.targeting_android IS NOT NULL)) THEN 'Any'::text ELSE NULL::text END) -> Sort (cost=356113.02..356113.52 rows=200 width=52) (actual time=1192.245..1193.084 rows=4280 loops=1) Sort Key: (1), (CASE WHEN ((fa.targeting_ios IS NOT NULL) AND (fa.targeting_android IS NOT NULL)) THEN 'iOS and Android'::text WHEN ((fa.targeting_ios IS NOT NULL) AND (fa.targeting_android IS NULL)) THEN 'iOS'::text WHEN ((fa.targeting_ios IS NULL) AND (fa.targeting_android IS NOT NULL)) THEN 'Android'::text WHEN ((fa.targeting_ios IS NOT NULL) AND (fa.targeting_android IS NOT NULL)) THEN 'Any'::text ELSE NULL::text END) Sort Method: quicksort Memory: 395kB -> Merge Full Join (cost=356063.98..356105.38 rows=200 width=52) (actual time=1183.640..1190.639 rows=4280 loops=1) Merge Cond: (fa.id = "*SELECT* 1".campaign_id) -> Group (cost=179905.47..179905.84 rows=1 width=44) (actual time=85.440..90.013 rows=4280 loops=1) Group Key: fa.id, (CASE WHEN ((fa.targeting_ios IS NOT NULL) AND (fa.targeting_android IS NOT NULL)) THEN 'iOS and Android'::text WHEN ((fa.targeting_ios IS NOT NULL) AND (fa.targeting_android IS NULL)) THEN 'iOS'::text WHEN ((fa.targeting_ios IS NULL) AND (fa.targeting_android IS NOT NULL)) THEN 'Android'::text WHEN ((fa.targeting_ios IS NOT NULL) AND (fa.targeting_android IS NOT NULL)) THEN 'Any'::text ELSE NULL::text END) -> Gather Merge (cost=179905.47..179905.83 rows=3 width=40) (actual time=85.438..87.984 rows=4280 loops=1) Workers Planned: 3 Workers Launched: 3 -> Group (cost=178905.43..178905.44 rows=1 width=40) (actual time=79.332..80.143 rows=1070 loops=4) Group Key: fa.id, (CASE WHEN ((fa.targeting_ios IS NOT NULL) AND (fa.targeting_android IS NOT NULL)) THEN 'iOS and Android'::text WHEN ((fa.targeting_ios IS NOT NULL) AND (fa.targeting_android IS NULL)) THEN 'iOS'::text WHEN ((fa.targeting_ios IS NULL) AND (fa.targeting_android IS NOT NULL)) THEN 'Android'::text WHEN ((fa.targeting_ios IS NOT NULL) AND (fa.targeting_android IS NOT NULL)) THEN 'Any'::text ELSE NULL::text END) -> Sort (cost=178905.43..178905.43 rows=1 width=40) (actual time=79.329..79.567 rows=1070 loops=4) Sort Key: fa.id, (CASE WHEN ((fa.targeting_ios IS NOT NULL) AND (fa.targeting_android IS NOT NULL)) THEN 'iOS and Android'::text WHEN ((fa.targeting_ios IS NOT NULL) AND (fa.targeting_android IS NULL)) THEN 'iOS'::text WHEN ((fa.targeting_ios IS NULL) AND (fa.targeting_android IS NOT NULL)) THEN 'Android'::text WHEN ((fa.targeting_ios IS NOT NULL) AND (fa.targeting_android IS NOT NULL)) THEN 'Any'::text ELSE NULL::text END) Sort Method: quicksort Memory: 97kB Worker 0: Sort Method: quicksort Memory: 72kB Worker 1: Sort Method: quicksort Memory: 99kB Worker 2: Sort Method: quicksort Memory: 103kB -> Parallel Hash Join (cost=167790.90..178905.42 rows=1 width=40) (actual time=70.443..78.798 rows=1070 loops=4) Hash Cond: (fc.id = fa.campaign) -> Parallel Seq Scan on fb_campaigns fc (cost=0.00..11113.68 rows=74 width=8) (actual time=0.090..7.951 rows=174 loops=4) Filter: ((account IS NULL) OR (account = ANY ('{128091690677840,205971526867688,183647115790295}'::bigint[]))) Rows Removed by Filter: 49408 -> Parallel Hash (cost=167790.81..167790.81 rows=7 width=64) (actual time=70.253..70.253 rows=1070 loops=4) Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 440kB -> Parallel Seq Scan on fb_adsets fa (cost=0.00..167790.81 rows=7 width=64) (actual time=0.988..55.405 rows=1070 loops=4) Filter: ((account = ANY ('{128091690677840,205971526867688,183647115790295}'::bigint[])) AND (account = ANY ('{128091690677840,205971526867688,183647115790295}'::bigint[]))) Rows Removed by Filter: 198077 -> Finalize GroupAggregate (cost=176158.52..176197.01 rows=200 width=160) (actual time=1098.196..1098.694 rows=64 loops=1) Group Key: "*SELECT* 1".campaign_id -> Gather Merge (cost=176158.52..176192.97 rows=272 width=24) (actual time=1098.160..1141.270 rows=181 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial GroupAggregate (cost=175158.49..175161.55 rows=136 width=24) (actual time=1088.396..1088.791 rows=60 loops=3) Group Key: "*SELECT* 1".campaign_id -> Sort (cost=175158.49..175158.83 rows=136 width=22) (actual time=1088.377..1088.547 rows=675 loops=3) Sort Key: "*SELECT* 1".campaign_id Sort Method: quicksort Memory: 73kB Worker 0: Sort Method: quicksort Memory: 80kB Worker 1: Sort Method: quicksort Memory: 79kB -> Parallel Hash Left Join (cost=90647.99..175153.67 rows=136 width=22) (actual time=963.744..1088.079 rows=675 loops=3) Hash Cond: (("*SELECT* 1".logdate = ud.logdate) AND ("*SELECT* 1".account_id = ud.account) AND ("*SELECT* 1".adgroup_id = ud.adgroup)) -> Parallel Hash Join (cost=11673.89..91194.54 rows=136 width=42) (actual time=64.276..76.694 rows=675 loops=3) Hash Cond: ("*SELECT* 1".campaign_group_id = c.id) -> Parallel Append (cost=0.42..79520.71 rows=140 width=48) (actual time=6.548..18.083 rows=675 loops=3) -> Subquery Scan on "*SELECT* 1" (cost=0.42..2.67 rows=1 width=48) (actual time=0.016..0.016 rows=0 loops=1) -> Index Scan using fb_ad_activity_daily2_pkey on fb_ad_activity_daily (cost=0.42..2.66 rows=1 width=1618) (actual time=0.015..0.015 rows=0 loops=1) Index Cond: ((logdate >= '2019-11-01'::date) AND (logdate <= '2019-11-17'::date) AND (logdate >= '2019-11-01'::date) AND (logdate <= '2019-11-17'::date)) Filter: ((account_id = ANY ('{128091690677840,205971526867688,183647115790295}'::bigint[])) AND (account_id = ANY ('{128091690677840,205971526867688,183647115790295}'::bigint[]))) -> Subquery Scan on "*SELECT* 2" (cost=0.56..79516.88 rows=326 width=48) (actual time=6.541..17.792 rows=675 loops=3) -> Parallel Append (cost=0.56..79513.62 rows=192 width=1618) (actual time=6.539..17.471 rows=675 loops=3) -> Parallel Index Scan using fb_ad_activity_daily_archive_2019_pkey on fb_ad_activity_daily_archive_2019 (cost=0.56..79512.66 rows=192 width=1618) (actual time=6.538..17.169 rows=675 loops=3) Index Cond: ((logdate >= '2019-11-01'::date) AND (logdate <= '2019-11-17'::date) AND (logdate >= '2019-11-01'::date) AND (logdate <= '2019-11-17'::date) AND (account_id = ANY ('{128091690677840,205971526867688,183647115790295}'::bigint[])) AND (account_id = ANY ('{128091690677840,205971526867688,183647115790295}'::bigint[]))) -> Parallel Hash (cost=10873.76..10873.76 rows=63976 width=10) (actual time=57.291..57.292 rows=66109 loops=3) Buckets: 262144 Batches: 1 Memory Usage: 11424kB -> Parallel Seq Scan on fb_campaigns c (cost=0.00..10873.76 rows=63976 width=10) (actual time=0.008..28.978 rows=66109 loops=3) -> Parallel Hash (cost=60953.22..60953.22 rows=771422 width=20) (actual time=875.479..875.479 rows=1031137 loops=3) Buckets: 131072 Batches: 32 Memory Usage: 6432kB -> Parallel Seq Scan on ca_ud_conversions ud (cost=0.00..60953.22 rows=771422 width=20) (actual time=0.004..405.186 rows=1031137 loops=3) Planning Time: 3.630 ms Execution Time: 1238.495 ms -- issue does not occur SET max_parallel_workers_per_gather TO 0; set enable_nestloop TO 0; GroupAggregate (cost=466729.81..466732.32 rows=1 width=76) (actual time=2866.611..2867.687 rows=3 loops=1) Group Key: (1), (CASE WHEN ((fa.targeting_ios IS NOT NULL) AND (fa.targeting_android IS NOT NULL)) THEN 'iOS and Android'::text WHEN ((fa.targeting_ios IS NOT NULL) AND (fa.targeting_android IS NULL)) THEN 'iOS'::text WHEN ((fa.targeting_ios IS NULL) AND (fa.targeting_android IS NOT NULL)) THEN 'Android'::text WHEN ((fa.targeting_ios IS NOT NULL) AND (fa.targeting_android IS NOT NULL)) THEN 'Any'::text ELSE NULL::text END) -> Sort (cost=466729.81..466730.31 rows=200 width=52) (actual time=2865.528..2866.368 rows=4280 loops=1) Sort Key: (1), (CASE WHEN ((fa.targeting_ios IS NOT NULL) AND (fa.targeting_android IS NOT NULL)) THEN 'iOS and Android'::text WHEN ((fa.targeting_ios IS NOT NULL) AND (fa.targeting_android IS NULL)) THEN 'iOS'::text WHEN ((fa.targeting_ios IS NULL) AND (fa.targeting_android IS NOT NULL)) THEN 'Android'::text WHEN ((fa.targeting_ios IS NOT NULL) AND (fa.targeting_android IS NOT NULL)) THEN 'Any'::text ELSE NULL::text END) Sort Method: quicksort Memory: 395kB -> Merge Full Join (cost=466713.55..466722.17 rows=200 width=52) (actual time=2857.653..2863.873 rows=4280 loops=1) Merge Cond: (fa.id = "*SELECT* 1".campaign_id) -> Group (cost=191854.00..191854.01 rows=1 width=44) (actual time=246.292..249.519 rows=4280 loops=1) Group Key: fa.id, (CASE WHEN ((fa.targeting_ios IS NOT NULL) AND (fa.targeting_android IS NOT NULL)) THEN 'iOS and Android'::text WHEN ((fa.targeting_ios IS NOT NULL) AND (fa.targeting_android IS NULL)) THEN 'iOS'::text WHEN ((fa.targeting_ios IS NULL) AND (fa.targeting_android IS NOT NULL)) THEN 'Android'::text WHEN ((fa.targeting_ios IS NOT NULL) AND (fa.targeting_android IS NOT NULL)) THEN 'Any'::text ELSE NULL::text END) -> Sort (cost=191854.00..191854.00 rows=1 width=40) (actual time=246.288..247.277 rows=4280 loops=1) Sort Key: fa.id, (CASE WHEN ((fa.targeting_ios IS NOT NULL) AND (fa.targeting_android IS NOT NULL)) THEN 'iOS and Android'::text WHEN ((fa.targeting_ios IS NOT NULL) AND (fa.targeting_android IS NULL)) THEN 'iOS'::text WHEN ((fa.targeting_ios IS NULL) AND (fa.targeting_android IS NOT NULL)) THEN 'Android'::text WHEN ((fa.targeting_ios IS NOT NULL) AND (fa.targeting_android IS NOT NULL)) THEN 'Any'::text ELSE NULL::text END) Sort Method: quicksort Memory: 393kB -> Hash Join (cost=12963.85..191853.99 rows=1 width=40) (actual time=31.233..244.131 rows=4280 loops=1) Hash Cond: (fa.campaign = fc.id) -> Seq Scan on fb_adsets fa (cost=0.00..178890.05 rows=34 width=64) (actual time=0.109..210.360 rows=4280 loops=1) Filter: ((account = ANY ('{128091690677840,205971526867688,183647115790295}'::bigint[])) AND (account = ANY ('{128091690677840,205971526867688,183647115790295}'::bigint[]))) Rows Removed by Filter: 792308 -> Hash (cost=12961.00..12961.00 rows=228 width=8) (actual time=31.110..31.111 rows=694 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 36kB -> Seq Scan on fb_campaigns fc (cost=0.00..12961.00 rows=228 width=8) (actual time=0.013..30.894 rows=694 loops=1) Filter: ((account IS NULL) OR (account = ANY ('{128091690677840,205971526867688,183647115790295}'::bigint[]))) Rows Removed by Filter: 197633 -> GroupAggregate (cost=274859.55..274865.64 rows=200 width=160) (actual time=2611.356..2612.484 rows=64 loops=1) Group Key: "*SELECT* 1".campaign_id -> Sort (cost=274859.55..274860.37 rows=327 width=22) (actual time=2611.327..2611.831 rows=2026 loops=1) Sort Key: "*SELECT* 1".campaign_id Sort Method: quicksort Memory: 207kB -> Hash Left Join (cost=171842.20..274845.89 rows=327 width=22) (actual time=2334.961..2610.504 rows=2026 loops=1) Hash Cond: (("*SELECT* 1".logdate = ud.logdate) AND ("*SELECT* 1".account_id = ud.account) AND ("*SELECT* 1".adgroup_id = ud.adgroup)) -> Hash Join (cost=15665.78..96165.51 rows=327 width=42) (actual time=156.656..198.283 rows=2026 loops=1) Hash Cond: ("*SELECT* 1".campaign_group_id = c.id) -> Append (cost=0.42..79524.29 rows=327 width=48) (actual time=13.125..45.025 rows=2026 loops=1) -> Subquery Scan on "*SELECT* 1" (cost=0.42..2.67 rows=1 width=48) (actual time=0.012..0.012 rows=0 loops=1) -> Index Scan using fb_ad_activity_daily2_pkey on fb_ad_activity_daily (cost=0.42..2.66 rows=1 width=1618) (actual time=0.011..0.011 rows=0 loops=1) Index Cond: ((logdate >= '2019-11-01'::date) AND (logdate <= '2019-11-17'::date) AND (logdate >= '2019-11-01'::date) AND (logdate <= '2019-11-17'::date)) Filter: ((account_id = ANY ('{128091690677840,205971526867688,183647115790295}'::bigint[])) AND (account_id = ANY ('{128091690677840,205971526867688,183647115790295}'::bigint[]))) -> Subquery Scan on "*SELECT* 2" (cost=0.56..79519.99 rows=326 width=48) (actual time=13.111..44.245 rows=2026 loops=1) -> Append (cost=0.56..79516.73 rows=326 width=1618) (actual time=13.110..43.372 rows=2026 loops=1) -> Index Scan using fb_ad_activity_daily_archive_2019_pkey on fb_ad_activity_daily_archive_2019 (cost=0.56..79515.10 rows=326 width=1618) (actual time=13.108..42.569 rows=2026 loops=1) Index Cond: ((logdate >= '2019-11-01'::date) AND (logdate <= '2019-11-17'::date) AND (logdate >= '2019-11-01'::date) AND (logdate <= '2019-11-17'::date) AND (account_id = ANY ('{128091690677840,205971526867688,183647115790295}'::bigint[])) AND (account_id = ANY ('{128091690677840,205971526867688,183647115790295}'::bigint[]))) -> Hash (cost=12217.27..12217.27 rows=198327 width=10) (actual time=143.122..143.122 rows=198327 loops=1) Buckets: 262144 Batches: 2 Memory Usage: 6693kB -> Seq Scan on fb_campaigns c (cost=0.00..12217.27 rows=198327 width=10) (actual time=0.007..74.000 rows=198327 loops=1) -> Hash (cost=84095.88..84095.88 rows=3085688 width=20) (actual time=2177.119..2177.119 rows=3093410 loops=1) Buckets: 131072 Batches: 32 Memory Usage: 6348kB -> Seq Scan on ca_ud_conversions ud (cost=0.00..84095.88 rows=3085688 width=20) (actual time=0.005..1005.261 rows=3093410 loops=1) Planning Time: 3.733 ms Execution Time: 2868.068 ms