explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Psmm : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #mUjw; plan #w4Gn; plan #UHOX8; plan #t8d; plan #8X5E

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 55,131.783 55,131.783 ↑ 6.0 2 1

CTE Scan on output output (cost=889.01..889.58 rows=12 width=2,480) (actual time=55,109.526..55,131.783 rows=2 loops=1)

  • Filter: ((output.viewable_impressions <> '0'::numeric) OR (output.media_type_goal_value <> '0'::numeric) OR (output.cpc <> '0'::numeric) OR (output.completion_rate <> '0'::numeric) OR (output.cost <> '0'::numeric) OR (output.cpm <> '0'::numeric) OR (output.ctr_percents <> '0'::numeric) OR (output.complete_views_video <> '0'::numeric) OR (output.media_type_goal_cost_cpa <> '0'::numeric) OR (output.impressions <> '0'::numeric) OR (output.clicks <> '0'::numeric))
  • Buffers: shared hit=174,793, temp read=8,025 written=9,243
2.          

CTE gen_met

3. 11.691 75.140 ↓ 2,474.6 29,695 1

Nested Loop (cost=5.11..345.95 rows=12 width=261) (actual time=0.032..75.14 rows=29,695 loops=1)

  • Buffers: shared hit=85,579
4. 8.755 33.754 ↓ 2,474.6 29,695 1

Nested Loop (cost=4.97..343.86 rows=12 width=220) (actual time=0.03..33.754 rows=29,695 loops=1)

  • Buffers: shared hit=26,189
5. 0.018 0.023 ↑ 1.0 16 1

Bitmap Heap Scan on source_campaign source_campaign_ids (cost=4.41..59.94 rows=16 width=141) (actual time=0.007..0.023 rows=16 loops=1)

  • Heap Blocks: exact=13
  • Buffers: shared hit=15
6. 0.005 0.005 ↑ 1.0 16 1

Bitmap Index Scan on source_campaign_advertiser_bench_id (cost=0..4.4 rows=16 width=0) (actual time=0.005..0.005 rows=16 loops=1)

  • Index Cond: ((source_campaign_ids.advertiser_bench_id)::text = '9sso6JNx9HkfiXynC'::text)
  • Buffers: shared hit=2
7. 24.976 24.976 ↓ 1,856.0 1,856 16

Index Scan using gen_met_test on general_metrics_cost general_metrics (cost=0.56..17.73 rows=1 width=79) (actual time=0.002..1.561 rows=1,856 loops=16)

  • Index Cond: ((general_metrics.source_campaign_id = source_campaign_ids.id) AND (general_metrics.source_campaign_id = ANY ('{-8866189128169111784,-7823823792601297793,-3987065537626353062}'::bigint[])) AND (general_metrics.date >= '2020-08-02'::date) AND (general_metrics.date <= '2020-09-01'::date))
  • Buffers: shared hit=26,174
8. 29.695 29.695 ↑ 1.0 1 29,695

Index Scan using ext_device_type_pkey on ext_device_type ext_device_type_ids (cost=0.14..0.16 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=29,695)

  • Index Cond: (ext_device_type_ids.id = general_metrics.ext_device_type_id)
  • Buffers: shared hit=59,390
9.          

CTE conv_met

10. 0.002 0.089 ↓ 2.0 2 1

Nested Loop (cost=61.26..247.14 rows=1 width=281) (actual time=0.08..0.089 rows=2 loops=1)

  • Buffers: shared hit=41
11. 0.001 0.083 ↓ 2.0 2 1

Nested Loop (cost=61.12..246.58 rows=1 width=248) (actual time=0.076..0.083 rows=2 loops=1)

  • Buffers: shared hit=37
12. 0.007 0.072 ↓ 2.0 2 1

Hash Join (cost=60.7..238.33 rows=1 width=221) (actual time=0.07..0.072 rows=2 loops=1)

  • Buffers: shared hit=29
13. 0.019 0.019 ↑ 20.5 2 1

Index Scan using conv_met_test on conversion conversion_metrics (cost=0.56..178.03 rows=41 width=80) (actual time=0.017..0.019 rows=2 loops=1)

  • Index Cond: ((conversion_metrics.source_campaign_id = ANY ('{-8866189128169111784,-7823823792601297793,-3987065537626353062}'::bigint[])) AND (conversion_metrics.date >= '2020-08-02'::date) AND (conversion_metrics.date <= '2020-09-01'::date))
  • Buffers: shared hit=14
14. 0.010 0.046 ↑ 1.0 16 1

Hash (cost=59.94..59.94 rows=16 width=141) (actual time=0.046..0.046 rows=16 loops=1)

  • Buffers: shared hit=15
15. 0.022 0.036 ↑ 1.0 16 1

Bitmap Heap Scan on source_campaign source_campaign_ids_1 (cost=4.41..59.94 rows=16 width=141) (actual time=0.018..0.036 rows=16 loops=1)

  • Heap Blocks: exact=13
  • Buffers: shared hit=15
16. 0.014 0.014 ↑ 1.0 16 1

Bitmap Index Scan on source_campaign_advertiser_bench_id (cost=0..4.4 rows=16 width=0) (actual time=0.013..0.014 rows=16 loops=1)

  • Index Cond: ((source_campaign_ids_1.advertiser_bench_id)::text = '9sso6JNx9HkfiXynC'::text)
  • Buffers: shared hit=2
17. 0.010 0.010 ↑ 1.0 1 2

Index Scan using pixel_pkey on pixel pixel (cost=0.42..8.24 rows=1 width=27) (actual time=0.004..0.005 rows=1 loops=2)

  • Index Cond: (pixel.id = conversion_metrics.pixel_id)
  • Buffers: shared hit=8
18. 0.004 0.004 ↑ 1.0 1 2

Index Scan using ext_device_type_pkey on ext_device_type ext_device_type (cost=0.14..0.55 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=2)

  • Index Cond: (ext_device_type.id = conversion_metrics.ext_device_type_id)
  • Buffers: shared hit=4
19.          

CTE conv_met_gen_met_lvl

20. 0.011 0.103 ↑ 1.0 1 1

Aggregate (cost=0.06..0.08 rows=1 width=256) (actual time=0.103..0.103 rows=1 loops=1)

  • Buffers: shared hit=41
21. 0.092 0.092 ↓ 2.0 2 1

CTE Scan on conv_met conv_met (cost=0..0.02 rows=1 width=88) (actual time=0.081..0.092 rows=2 loops=1)

  • Buffers: shared hit=41
22.          

CTE goals

23. 5.376 186.070 ↓ 3,299.5 59,391 1

Append (cost=0..1.44 rows=18 width=402) (actual time=73.136..186.07 rows=59,391 loops=1)

  • Buffers: shared hit=85,562, temp read=2,874 written=957
24. 5.611 121.224 ↓ 3,554.3 10,663 1

Subquery Scan on *SELECT* 1 (cost=0..0.34 rows=3 width=402) (actual time=73.136..121.224 rows=10,663 loops=1)

  • Buffers: shared hit=85,562, temp written=956
25. 115.613 115.613 ↓ 3,554.3 10,663 1

CTE Scan on gen_met gen_met (cost=0..0.29 rows=3 width=318) (actual time=73.129..115.613 rows=10,663 loops=1)

  • Filter: ((gen_met.channel_bench_id)::text = ANY ('{N3GohqX5pWh4KRh5N,xCXNXwG98SFjqubNE,hJQDHf369Z6P7mFqg}'::text[]))
  • Buffers: shared hit=85,562, temp written=956
26. 24.038 24.038 ↓ 19,032.0 19,032 1

CTE Scan on gen_met gen_met_1 (cost=0..0.29 rows=1 width=402) (actual time=0.02..24.038 rows=19,032 loops=1)

  • Filter: ((gen_met_1.channel_bench_id)::text = 'w8Y48XyiEhusjND4b'::text)
  • Buffers: temp read=958 written=1
27. 6.785 6.785 ↓ 0.0 0 1

CTE Scan on gen_met gen_met_2 (cost=0..0.29 rows=1 width=402) (actual time=6.785..6.785 rows=0 loops=1)

  • Filter: ((gen_met_2.channel_bench_id)::text = 'kMky6w52aQGZEeb6s'::text)
  • Buffers: temp read=958
28. 0.003 0.010 ↑ 1.0 1 1

Subquery Scan on *SELECT* 4 (cost=0..0.06 rows=1 width=402) (actual time=0.009..0.01 rows=1 loops=1)

29. 0.007 0.007 ↑ 1.0 1 1

CTE Scan on conv_met conv_met_1 (cost=0..0.04 rows=1 width=318) (actual time=0.006..0.007 rows=1 loops=1)

  • Filter: (((conv_met_1.conversion_name)::text = 'Enquiries'::text) AND ((((conv_met_1.campaign_bench_id)::text = 'xHd7yuXcAM8iYcg7K'::text) AND (conv_met_1.data_source_id = '-2806866175954505392'::bigint) AND (conv_met_1.media_type_id = '3205723216801264461'::bigint) AND (conv_met_1.platform_id <> '-3588384266460475734'::bigint)) OR (((conv_met_1.campaign_bench_id)::text = 'xHd7yuXcAM8iYcg7K'::text) AND (conv_met_1.data_source_id <> '-2806866175954505392'::bigint) AND (conv_met_1.media_type_id <> '3205723216801264461'::bigint) AND (conv_met_1.platform_id <> '-3588384266460475734'::bigint)) OR ((conv_met_1.campaign_bench_id)::text <> 'xHd7yuXcAM8iYcg7K'::text)))
30. 14.193 28.637 ↓ 2,474.6 29,695 1

Subquery Scan on *SELECT* 5 (cost=0..0.45 rows=12 width=402) (actual time=0.006..28.637 rows=29,695 loops=1)

  • Buffers: temp read=958
31. 14.444 14.444 ↓ 2,474.6 29,695 1

CTE Scan on gen_met gen_met_3 (cost=0..0.24 rows=12 width=318) (actual time=0.004..14.444 rows=29,695 loops=1)

  • Buffers: temp read=958
32.          

CTE media_type_goals

33. 10.684 343.205 ↓ 10,664.0 10,664 1

Aggregate (cost=99.47..99.55 rows=1 width=104) (actual time=331.161..343.205 rows=10,664 loops=1)

  • Buffers: shared hit=85,603, temp read=4,408 written=3,949
34. 15.104 332.521 ↓ 10,664.0 10,664 1

Sort (cost=99.47..99.47 rows=1 width=204) (actual time=331.142..332.521 rows=10,664 loops=1)

  • Sort Key: goals.allocation_id, goals.source_campaign_id, goals.source_adset_id, goals.source_ad_id, goals.data_source_id, goals.ext_device_type_id, goals.device_type_id, goals.date, goals.time_of_day_id
  • Sort Method: quicksort Memory: 3,165kB
  • Buffers: shared hit=85,603, temp read=4,408 written=3,949
35. 26.208 317.417 ↓ 10,664.0 10,664 1

Hash Join (cost=0.72..99.45 rows=1 width=204) (actual time=300.39..317.417 rows=10,664 loops=1)

  • Buffers: shared hit=85,603, temp read=4,408 written=3,949
36. 0.503 0.503 ↑ 1.0 2,082 1

Seq Scan on media_type_goal media_type_goal_mapping (cost=0..62.29 rows=2,082 width=94) (actual time=0.007..0.503 rows=2,082 loops=1)

  • Filter: (media_type_goal_mapping.is_archived IS NOT TRUE)
  • Buffers: shared hit=41
37. 39.664 290.706 ↓ 3,299.5 59,391 1

Hash (cost=0.36..0.36 rows=18 width=402) (actual time=290.706..290.706 rows=59,391 loops=1)

  • Buffers: shared hit=85,562, temp read=2,874 written=3,915
38. 251.042 251.042 ↓ 3,299.5 59,391 1

CTE Scan on goals goals (cost=0..0.36 rows=18 width=402) (actual time=73.139..251.042 rows=59,391 loops=1)

  • Buffers: shared hit=85,562, temp read=2,874 written=2,421
39.          

CTE result

40. 32,381.660 54,763.584 ↓ 2,474.7 29,696 1

Nested Loop (cost=0.33..100.9 rows=12 width=741) (actual time=348.35..54,763.584 rows=29,696 loops=1)

  • Buffers: shared hit=174,749, temp read=5,366 written=3,949
41. 19.977 109.924 ↓ 2,474.7 29,696 1

Nested Loop (cost=0.33..100.38 rows=12 width=906) (actual time=0.153..109.924 rows=29,696 loops=1)

  • Buffers: shared hit=89,146, temp read=958
42. 19.805 30.555 ↓ 2,474.7 29,696 1

Hash Join (cost=0.05..0.66 rows=12 width=706) (actual time=0.148..30.555 rows=29,696 loops=1)

  • Buffers: shared hit=58, temp read=958
43. 10.643 10.643 ↓ 2,474.6 29,695 1

CTE Scan on gen_met gen_met_4 (cost=0..0.24 rows=12 width=450) (actual time=0.033..10.643 rows=29,695 loops=1)

  • Buffers: shared hit=17, temp read=958
44. 0.003 0.107 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=256) (actual time=0.107..0.107 rows=1 loops=1)

  • Buffers: shared hit=41
45. 0.104 0.104 ↑ 1.0 1 1

CTE Scan on conv_met_gen_met_lvl conv_met_gen_met_lvl (cost=0..0.02 rows=1 width=256) (actual time=0.104..0.104 rows=1 loops=1)

  • Buffers: shared hit=41
46. 59.392 59.392 ↑ 1.0 1 29,696

Index Scan using source_campaign_pkey on source_campaign source_campaign (cost=0.28..8.3 rows=1 width=200) (actual time=0.002..0.002 rows=1 loops=29,696)

  • Index Cond: (source_campaign.id = COALESCE(gen_met_4.source_campaign_id, conv_met_gen_met_lvl.source_campaign_id))
  • Buffers: shared hit=89,088
47. 22,272.000 22,272.000 ↓ 10,664.0 10,664 29,696

CTE Scan on media_type_goals media_type_goals (cost=0..0.02 rows=1 width=104) (actual time=0.011..0.75 rows=10,664 loops=29,696)

  • Buffers: shared hit=85,603, temp read=4,408 written=3,949
48.          

CTE result_goal_names

49. 29.347 54,919.725 ↓ 2,474.7 29,696 1

Hash Join (cost=86.32..91.54 rows=12 width=2,816) (actual time=54,897.905..54,919.725 rows=29,696 loops=1)

  • Buffers: shared hit=174,793, temp read=7,420 written=7,298
50. 0.022 0.022 ↑ 1.0 155 1

Seq Scan on campaign_goal campaign_goal_mapping (cost=0..4.55 rows=155 width=19) (actual time=0.01..0.022 rows=155 loops=1)

  • Filter: (campaign_goal_mapping.is_archived IS NOT TRUE)
  • Buffers: shared hit=3
51. 21.614 54,890.356 ↓ 2,474.7 29,696 1

Hash (cost=86.17..86.17 rows=12 width=2,824) (actual time=54,890.356..54,890.356 rows=29,696 loops=1)

  • Buffers: shared hit=174,790, temp read=5,849 written=7,295
52. 18.791 54,868.742 ↓ 2,474.7 29,696 1

Hash Join (cost=0.45..86.17 rows=12 width=2,824) (actual time=54,850.017..54,868.742 rows=29,696 loops=1)

  • Buffers: shared hit=174,790, temp read=5,849 written=5,729
53. 0.203 0.203 ↑ 1.0 2,082 1

Seq Scan on media_type_goal media_type_goal_mapping_1 (cost=0..62.29 rows=2,082 width=74) (actual time=0.007..0.203 rows=2,082 loops=1)

  • Filter: (media_type_goal_mapping_1.is_archived IS NOT TRUE)
  • Buffers: shared hit=41
54. 28.641 54,849.748 ↓ 2,474.7 29,696 1

Hash (cost=0.24..0.24 rows=12 width=2,750) (actual time=54,849.748..54,849.748 rows=29,696 loops=1)

  • Buffers: shared hit=174,749, temp read=5,366 written=5,709
55. 54,821.107 54,821.107 ↓ 2,474.7 29,696 1

CTE Scan on result result (cost=0..0.24 rows=12 width=2,750) (actual time=348.354..54,821.107 rows=29,696 loops=1)

  • Buffers: shared hit=174,749, temp read=5,366 written=5,248
56.          

CTE output

57. 27.918 55,131.776 ↑ 6.0 2 1

Aggregate (cost=0.46..2.41 rows=12 width=2,516) (actual time=55,109.521..55,131.776 rows=2 loops=1)

  • Buffers: shared hit=174,793, temp read=8,025 written=9,243
58. 146.498 55,103.858 ↓ 2,474.7 29,696 1

Sort (cost=0.46..0.49 rows=12 width=2,252) (actual time=55,097.333..55,103.858 rows=29,696 loops=1)

  • Sort Key: result_goal_names.layer_seq, result_goal_names.channel_name, result_goal_names.media_type_name, result_goal_names.platform_name, result_goal_names.media_type_goal_name, result_goal_names.media_type_goal_target, result_goal_names.layer_id, result_goal_names.layer_name, result_goal_names.channel_id, result_goal_names.media_type_id, result_goal_names.platform_id
  • Sort Method: external sort Disk: 4,840kB
  • Buffers: shared hit=174,793, temp read=8,025 written=9,243
59. 54,957.360 54,957.360 ↓ 2,474.7 29,696 1

CTE Scan on result_goal_names result_goal_names (cost=0..0.24 rows=12 width=2,252) (actual time=54,897.91..54,957.36 rows=29,696 loops=1)

  • Buffers: shared hit=174,793, temp read=7,420 written=8,638
Planning time : 4.453 ms
Execution time : 55,136.689 ms