explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UHOX8 : Optimization for: Optimization for: plan #mUjw; plan #w4Gn

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 57,138.904 57,138.904 ↑ 1.0 2 1

CTE Scan on output output (cost=471,415.6..471,415.69 rows=2 width=2,480) (actual time=57,116.552..57,138.904 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=490,340, temp read=82,659 written=8,570
2.          

CTE gen_met

3. 177.357 859.440 ↓ 14,847.5 29,695 1

Nested Loop (cost=0.84..344,298.63 rows=2 width=261) (actual time=764.466..859.44 rows=29,695 loops=1)

  • Buffers: shared hit=107,910, temp read=76,651 written=1,336
4. 0.033 0.033 ↑ 1.0 75 1

Seq Scan on ext_device_type ext_device_type_ids (cost=0..2.75 rows=75 width=16) (actual time=0.01..0.033 rows=75 loops=1)

  • Buffers: shared hit=2
5. 288.512 682.050 ↓ 14,847.5 29,695 75

Materialize (cost=0.84..344,293.64 rows=2 width=220) (actual time=3.856..9.094 rows=29,695 loops=75)

  • Buffers: shared hit=107,908, temp read=76,651 written=1,336
6. 56.033 393.538 ↓ 14,847.5 29,695 1

Nested Loop (cost=0.84..344,293.63 rows=2 width=220) (actual time=289.068..393.538 rows=29,695 loops=1)

  • Buffers: shared hit=107,908, temp read=5,685 written=378
7. 2.673 2.673 ↓ 2.3 16 1

Index Scan using source_campaign_pkey on source_campaign source_campaign_ids (cost=0.28..1,852.54 rows=7 width=141) (actual time=0.024..2.673 rows=16 loops=1)

  • Filter: ((source_campaign_ids.advertiser_bench_id)::text = '9sso6JNx9HkfiXynC'::text)
  • Buffers: shared hit=3,993
8. 61.588 334.832 ↓ 23.8 29,695 16

Materialize (cost=0.56..342,313.06 rows=1,249 width=79) (actual time=0.026..20.927 rows=29,695 loops=16)

  • Buffers: shared hit=103,915, temp read=5,685 written=378
9. 273.244 273.244 ↓ 23.8 29,695 1

Index Scan using calc_general_metrics_cost_date_ids on general_metrics_cost general_metrics (cost=0.56..342,306.82 rows=1,249 width=79) (actual time=0.342..273.244 rows=29,695 loops=1)

  • Index Cond: ((general_metrics.date >= '2020-08-02'::date) AND (general_metrics.date <= '2020-09-01'::date) AND (general_metrics.source_campaign_id = ANY ('{-8866189128169111784,-7823823792601297793,-3987065537626353062}'::bigint[])))
  • Buffers: shared hit=103,915
10.          

CTE conv_met

11. 0.022 72.796 ↓ 2.0 2 1

Nested Loop (cost=5.31..127,018.96 rows=1 width=281) (actual time=56.171..72.796 rows=2 loops=1)

  • Buffers: shared hit=26,078
12. 0.003 72.752 ↓ 2.0 2 1

Nested Loop (cost=5.31..127,015.27 rows=1 width=248) (actual time=56.144..72.752 rows=2 loops=1)

  • Buffers: shared hit=26,074
13. 0.012 72.735 ↓ 2.0 2 1

Nested Loop (cost=4.9..127,008.8 rows=1 width=221) (actual time=56.133..72.735 rows=2 loops=1)

  • Buffers: shared hit=26,066
14. 72.637 72.637 ↑ 247.5 2 1

Index Scan using fact_conversion_date_ids on conversion conversion_metrics (cost=0.56..126,926.89 rows=495 width=80) (actual time=56.057..72.637 rows=2 loops=1)

  • Index Cond: ((conversion_metrics.date >= '2020-08-02'::date) AND (conversion_metrics.date <= '2020-09-01'::date) AND (conversion_metrics.source_campaign_id = ANY ('{-8866189128169111784,-7823823792601297793,-3987065537626353062}'::bigint[])))
  • Buffers: shared hit=26,051
15. 0.025 0.086 ↓ 2.3 16 2

Materialize (cost=4.34..29.95 rows=7 width=141) (actual time=0.027..0.043 rows=16 loops=2)

  • Buffers: shared hit=15
16. 0.030 0.061 ↓ 2.3 16 1

Bitmap Heap Scan on source_campaign source_campaign_ids_1 (cost=4.34..29.92 rows=7 width=141) (actual time=0.038..0.061 rows=16 loops=1)

  • Heap Blocks: exact=13
  • Buffers: shared hit=15
17. 0.031 0.031 ↓ 2.3 16 1

Bitmap Index Scan on source_campaign_advertiser_bench_id (cost=0..4.33 rows=7 width=0) (actual time=0.031..0.031 rows=16 loops=1)

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

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

  • Index Cond: (pixel.id = conversion_metrics.pixel_id)
  • Buffers: shared hit=8
19. 0.022 0.022 ↑ 1.0 75 2

Seq Scan on ext_device_type ext_device_type (cost=0..2.75 rows=75 width=16) (actual time=0.005..0.011 rows=75 loops=2)

  • Buffers: shared hit=4
20.          

CTE conv_met_gen_met_lvl

21. 0.039 72.842 ↑ 1.0 1 1

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

  • Buffers: shared hit=26,078
22. 72.803 72.803 ↓ 2.0 2 1

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

  • Buffers: shared hit=26,078
23.          

CTE goals

24. 4.894 197.346 ↓ 8,484.4 59,391 1

Append (cost=0..0.36 rows=7 width=402) (actual time=4.072..197.346 rows=59,391 loops=1)

  • Buffers: shared hit=1, temp read=15,951 written=957
25. 5.227 132.889 ↓ 5,331.5 10,663 1

Subquery Scan on *SELECT* 1 (cost=0..0.08 rows=2 width=402) (actual time=4.072..132.889 rows=10,663 loops=1)

  • Buffers: shared hit=1, temp read=13,077 written=956
26. 127.662 127.662 ↓ 5,331.5 10,663 1

CTE Scan on gen_met gen_met (cost=0..0.05 rows=2 width=318) (actual time=4.066..127.662 rows=10,663 loops=1)

  • Filter: ((gen_met.channel_bench_id)::text = ANY ('{N3GohqX5pWh4KRh5N,xCXNXwG98SFjqubNE,hJQDHf369Z6P7mFqg}'::text[]))
  • Buffers: shared hit=1, temp read=13,077 written=956
27. 24.932 24.932 ↓ 19,032.0 19,032 1

CTE Scan on gen_met gen_met_1 (cost=0..0.06 rows=1 width=402) (actual time=0.019..24.932 rows=19,032 loops=1)

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

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

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

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

30. 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.007..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)))
31. 13.106 27.566 ↓ 14,847.5 29,695 1

Subquery Scan on *SELECT* 5 (cost=0..0.07 rows=2 width=402) (actual time=0.006..27.566 rows=29,695 loops=1)

  • Buffers: temp read=958
32. 14.460 14.460 ↓ 14,847.5 29,695 1

CTE Scan on gen_met gen_met_3 (cost=0..0.04 rows=2 width=318) (actual time=0.004..14.46 rows=29,695 loops=1)

  • Buffers: temp read=958
33.          

CTE media_type_goals

34. 10.220 474.357 ↓ 10,664.0 10,664 1

Aggregate (cost=58.39..58.47 rows=1 width=104) (actual time=463.158..474.357 rows=10,664 loops=1)

  • Buffers: shared hit=178,174, temp read=15,951 written=2,421
35. 14.118 464.137 ↓ 10,664.0 10,664 1

Sort (cost=58.39..58.39 rows=1 width=204) (actual time=463.135..464.137 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=178,174, temp read=15,951 written=2,421
36. 21.560 450.019 ↓ 10,664.0 10,664 1

Nested Loop (cost=0.28..58.38 rows=1 width=204) (actual time=4.093..450.019 rows=10,664 loops=1)

  • Buffers: shared hit=178,174, temp read=15,951 written=2,421
37. 250.286 250.286 ↓ 8,484.4 59,391 1

CTE Scan on goals goals (cost=0..0.14 rows=7 width=402) (actual time=4.073..250.286 rows=59,391 loops=1)

  • Buffers: shared hit=1, temp read=15,951 written=2,421
38. 178.173 178.173 ↓ 0.0 0 59,391

Index Scan using dim_media_type_goal_unique_keys on media_type_goal media_type_goal_mapping (cost=0.28..8.31 rows=1 width=94) (actual time=0.003..0.003 rows=0 loops=59,391)

  • Index Cond: (((media_type_goal_mapping.campaign_bench_id)::text = (goals.campaign_bench_id)::text) AND ((media_type_goal_mapping.layer_bench_id)::text = (goals.layer_bench_id)::text) AND ((media_type_goal_mapping.media_type_bench_id)::text = (goals.media_type_bench_id)::text))
  • Filter: ((media_type_goal_mapping.is_archived IS NOT TRUE) AND (goals.goal_id = md5(((media_type_goal_mapping.goal_id)::text || (media_type_goal_mapping.goal_name)::text))))
  • Buffers: shared hit=178,173
39.          

CTE result

40. 32,931.578 56,600.163 ↓ 14,848.0 29,696 1

Nested Loop (cost=0.33..16.88 rows=2 width=741) (actual time=1,316.647..56,600.163 rows=29,696 loops=1)

  • Buffers: shared hit=401,249, temp read=80,483 written=3,757
41. 17.173 951.145 ↓ 14,848.0 29,696 1

Nested Loop (cost=0.33..16.78 rows=2 width=906) (actual time=837.351..951.145 rows=29,696 loops=1)

  • Buffers: shared hit=223,075, temp read=64,532 written=1,336
42. 23.510 874.580 ↓ 14,848.0 29,696 1

Hash Join (cost=0.05..0.16 rows=2 width=706) (actual time=837.333..874.58 rows=29,696 loops=1)

  • Buffers: shared hit=133,987, temp read=64,532 written=1,336
43. 778.218 778.218 ↓ 14,847.5 29,695 1

CTE Scan on gen_met gen_met_4 (cost=0..0.04 rows=2 width=450) (actual time=764.469..778.218 rows=29,695 loops=1)

  • Buffers: shared hit=107,909, temp read=64,532 written=1,336
44. 0.006 72.852 ↑ 1.0 1 1

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

  • Buffers: shared hit=26,078
45. 72.846 72.846 ↑ 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=72.845..72.846 rows=1 loops=1)

  • Buffers: shared hit=26,078
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.001..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,717.440 22,717.440 ↓ 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.016..0.765 rows=10,664 loops=29,696)

  • Buffers: shared hit=178,174, temp read=15,951 written=2,421
48.          

CTE result_goal_names

49. 32.789 56,898.933 ↓ 14,848.0 29,696 1

Hash Join (cost=16.69..21.84 rows=2 width=2,816) (actual time=56,875.745..56,898.933 rows=29,696 loops=1)

  • Buffers: shared hit=490,340, temp read=82,054 written=6,625
50. 0.021 0.021 ↑ 1.0 155 1

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

  • Filter: (campaign_goal_mapping.is_archived IS NOT TRUE)
  • Buffers: shared hit=3
51. 49.133 56,866.123 ↓ 14,848.0 29,696 1

Hash (cost=16.66..16.66 rows=2 width=2,824) (actual time=56,866.123..56,866.123 rows=29,696 loops=1)

  • Buffers: shared hit=490,337, temp read=80,483 written=6,622
52. 43.480 56,816.990 ↓ 14,848.0 29,696 1

Nested Loop (cost=0.28..16.66 rows=2 width=2,824) (actual time=1,316.676..56,816.99 rows=29,696 loops=1)

  • Buffers: shared hit=490,337, temp read=80,483 written=5,056
53. 56,654.726 56,654.726 ↓ 14,848.0 29,696 1

CTE Scan on result result (cost=0..0.04 rows=2 width=2,750) (actual time=1,316.65..56,654.726 rows=29,696 loops=1)

  • Buffers: shared hit=401,249, temp read=80,483 written=5,056
54. 118.784 118.784 ↑ 1.0 1 29,696

Index Scan using dim_media_type_goal_unique_keys on media_type_goal media_type_goal_mapping_1 (cost=0.28..8.3 rows=1 width=74) (actual time=0.004..0.004 rows=1 loops=29,696)

  • Index Cond: (((result.campaign_bench_id)::text = (media_type_goal_mapping_1.campaign_bench_id)::text) AND ((result.layer_bench_id)::text = (media_type_goal_mapping_1.layer_bench_id)::text) AND ((result.media_type_bench_id)::text = (media_type_goal_mapping_1.media_type_bench_id)::text))
  • Filter: (media_type_goal_mapping_1.is_archived IS NOT TRUE)
  • Buffers: shared hit=89,088
55.          

CTE output

56. 28.634 57,138.892 ↑ 1.0 2 1

Aggregate (cost=0.05..0.38 rows=2 width=2,516) (actual time=57,116.546..57,138.892 rows=2 loops=1)

  • Buffers: shared hit=490,340, temp read=82,659 written=8,570
57. 170.307 57,110.258 ↓ 14,848.0 29,696 1

Sort (cost=0.05..0.06 rows=2 width=2,252) (actual time=57,097.558..57,110.258 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 merge Disk: 4,832kB
  • Buffers: shared hit=490,340, temp read=82,659 written=8,570
58. 56,939.951 56,939.951 ↓ 14,848.0 29,696 1

CTE Scan on result_goal_names result_goal_names (cost=0..0.04 rows=2 width=2,252) (actual time=56,875.763..56,939.951 rows=29,696 loops=1)

  • Buffers: shared hit=490,340, temp read=82,054 written=7,965
Planning time : 1.953 ms
Execution time : 57,145.25 ms