explain.depesz.com

PostgreSQL's explain analyze made readable

Result: w4Gn : Optimization for: plan #mUjw

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 61,490.865 61,490.865 ↑ 1.0 2 1

CTE Scan on output output (cost=472,379.47..472,379.56 rows=2 width=2,480) (actual time=61,458.14..61,490.865 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=491,442, temp read=94,168 written=8,867
2.          

CTE gen_met

3. 185.341 4,446.893 ↓ 14,847.5 29,695 1

Nested Loop (cost=0.84..344,923.79 rows=2 width=277) (actual time=4,346.469..4,446.893 rows=29,695 loops=1)

  • Buffers: shared hit=108,686, temp read=87,899 written=1,488
4. 0.052 0.052 ↑ 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.011..0.052 rows=75 loops=1)

  • Buffers: shared hit=2
5. 313.335 4,261.500 ↓ 14,847.5 29,695 75

Materialize (cost=0.84..344,918.79 rows=2 width=262) (actual time=4.494..56.82 rows=29,695 loops=75)

  • Buffers: shared hit=108,684, temp read=87,899 written=1,488
6. 2,338.625 3,948.165 ↓ 14,847.5 29,695 1

Nested Loop (cost=0.84..344,918.78 rows=2 width=262) (actual time=336.897..3,948.165 rows=29,695 loops=1)

  • Buffers: shared hit=108,684, temp read=5,685 written=378
7. 56.796 392.045 ↓ 14,847.5 29,695 1

Nested Loop (cost=0.84..344,568.35 rows=2 width=220) (actual time=335.795..392.045 rows=29,695 loops=1)

  • Buffers: shared hit=108,521, temp read=5,685 written=378
8. 2.641 2.641 ↓ 2.3 16 1

Index Scan using dim_source_campaign_campaign_id on source_campaign source_campaign_ids (cost=0.28..2,127.26 rows=7 width=141) (actual time=1.085..2.641 rows=16 loops=1)

  • Filter: ((source_campaign_ids.advertiser_bench_id)::text = '9sso6JNx9HkfiXynC'::text)
  • Buffers: shared hit=4,606
9. 63.464 332.608 ↓ 23.8 29,695 16

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

  • Buffers: shared hit=103,915, temp read=5,685 written=378
10. 269.144 269.144 ↓ 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.295..269.144 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
11. 1,216.066 1,217.495 ↓ 1.8 1,053 29,695

Materialize (cost=0..334.32 rows=586 width=42) (actual time=0..0.041 rows=1,053 loops=29,695)

  • Buffers: shared hit=163
12. 1.429 1.429 ↓ 1.8 1,053 1

Seq Scan on campaign_assignment campaign_assignment (cost=0..331.39 rows=586 width=42) (actual time=0.016..1.429 rows=1,053 loops=1)

  • Filter: ((campaign_assignment.role_id = '3385422206543899366'::bigint) AND COALESCE((campaign_assignment.start_date_utc <= now()), true) AND COALESCE((now() < campaign_assignment.end_date_utc), true))
  • Buffers: shared hit=163
13.          

CTE conv_met

14. 0.126 71.449 ↓ 2.0 2 1

Nested Loop (cost=5.31..127,357.68 rows=1 width=297) (actual time=54.371..71.449 rows=2 loops=1)

  • Buffers: shared hit=26,404
15. 0.014 68.853 ↓ 2.0 2 1

Nested Loop (cost=5.31..127,018.96 rows=1 width=264) (actual time=53.473..68.853 rows=2 loops=1)

  • Buffers: shared hit=26,078
16. 0.007 68.821 ↓ 2.0 2 1

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

  • Buffers: shared hit=26,074
17. 0.009 68.804 ↓ 2.0 2 1

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

  • Buffers: shared hit=26,066
18. 68.719 68.719 ↑ 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=53.376..68.719 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
19. 0.019 0.076 ↓ 2.3 16 2

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

  • Buffers: shared hit=15
20. 0.031 0.057 ↓ 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.034..0.057 rows=16 loops=1)

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

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

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

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

  • Index Cond: (pixel.id = conversion_metrics.pixel_id)
  • Buffers: shared hit=8
23. 0.018 0.018 ↑ 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.009 rows=75 loops=2)

  • Buffers: shared hit=4
24. 2.470 2.470 ↓ 1.8 1,053 2

Seq Scan on campaign_assignment campaign_assignment_1 (cost=0..331.39 rows=586 width=42) (actual time=0.006..1.235 rows=1,053 loops=2)

  • Filter: ((campaign_assignment_1.role_id = '3385422206543899366'::bigint) AND COALESCE((campaign_assignment_1.start_date_utc <= now()), true) AND COALESCE((now() < campaign_assignment_1.end_date_utc), true))
  • Buffers: shared hit=326
25.          

CTE conv_met_gen_met_lvl

26. 0.036 71.490 ↑ 1.0 1 1

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

  • Buffers: shared hit=26,404
27. 71.454 71.454 ↓ 2.0 2 1

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

  • Buffers: shared hit=26,404
28.          

CTE goals

29. 4.746 205.455 ↓ 8,484.4 59,391 1

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

  • Buffers: shared hit=1, temp read=18,595 written=1,015
30. 5.316 140.846 ↓ 5,331.5 10,663 1

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

  • Buffers: shared hit=1, temp read=15,547 written=1,014
31. 135.530 135.530 ↓ 5,331.5 10,663 1

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

  • Filter: ((gen_met.channel_bench_id)::text = ANY ('{N3GohqX5pWh4KRh5N,xCXNXwG98SFjqubNE,hJQDHf369Z6P7mFqg}'::text[]))
  • Buffers: shared hit=1, temp read=15,547 written=1,014
32. 25.343 25.343 ↓ 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..25.343 rows=19,032 loops=1)

  • Filter: ((gen_met_1.channel_bench_id)::text = 'w8Y48XyiEhusjND4b'::text)
  • Buffers: temp read=1,016 written=1
33. 7.396 7.396 ↓ 0.0 0 1

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

  • Filter: ((gen_met_2.channel_bench_id)::text = 'kMky6w52aQGZEeb6s'::text)
  • Buffers: temp read=1,016
34. 0.004 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)

35. 0.006 0.006 ↑ 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.006 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)))
36. 12.600 27.114 ↓ 14,847.5 29,695 1

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

  • Buffers: temp read=1,016
37. 14.514 14.514 ↓ 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.003..14.514 rows=29,695 loops=1)

  • Buffers: temp read=1,016
38.          

CTE media_type_goals

39. 10.074 477.848 ↓ 10,664.0 10,664 1

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

  • Buffers: shared hit=178,174, temp read=18,595 written=2,479
40. 13.930 467.774 ↓ 10,664.0 10,664 1

Sort (cost=58.39..58.39 rows=1 width=204) (actual time=466.823..467.774 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=18,595 written=2,479
41. 18.221 453.844 ↓ 10,664.0 10,664 1

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

  • Buffers: shared hit=178,174, temp read=18,595 written=2,479
42. 257.450 257.450 ↓ 8,484.4 59,391 1

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

  • Buffers: shared hit=1, temp read=18,595 written=2,479
43. 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
44.          

CTE result

45. 33,218.286 60,897.206 ↓ 14,848.0 29,696 1

Nested Loop (cost=0.33..16.88 rows=2 width=757) (actual time=4,900.779..60,897.206 rows=29,696 loops=1)

  • Buffers: shared hit=402,351, temp read=91,963 written=3,967
46. 25.314 4,545.736 ↓ 14,848.0 29,696 1

Nested Loop (cost=0.33..16.78 rows=2 width=922) (actual time=4,418.014..4,545.736 rows=29,696 loops=1)

  • Buffers: shared hit=224,177, temp read=73,368 written=1,488
47. 27.849 4,461.030 ↓ 14,848.0 29,696 1

Hash Join (cost=0.05..0.16 rows=2 width=722) (actual time=4,417.996..4,461.03 rows=29,696 loops=1)

  • Buffers: shared hit=135,089, temp read=73,368 written=1,488
48. 4,361.680 4,361.680 ↓ 14,847.5 29,695 1

CTE Scan on gen_met gen_met_4 (cost=0..0.04 rows=2 width=466) (actual time=4,346.473..4,361.68 rows=29,695 loops=1)

  • Buffers: shared hit=108,685, temp read=73,368 written=1,488
49. 0.005 71.501 ↑ 1.0 1 1

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

  • Buffers: shared hit=26,404
50. 71.496 71.496 ↑ 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=71.494..71.496 rows=1 loops=1)

  • Buffers: shared hit=26,404
51. 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
52. 23,133.184 23,133.184 ↓ 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.779 rows=10,664 loops=29,696)

  • Buffers: shared hit=178,174, temp read=18,595 written=2,479
53.          

CTE result_goal_names

54. 35.798 61,218.112 ↓ 14,848.0 29,696 1

Hash Join (cost=16.69..21.84 rows=2 width=2,832) (actual time=61,192.626..61,218.112 rows=29,696 loops=1)

  • Buffers: shared hit=491,442, temp read=93,563 written=6,893
55. 0.019 0.019 ↑ 1.0 155 1

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

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

Hash (cost=16.66..16.66 rows=2 width=2,840) (actual time=61,182.295..61,182.295 rows=29,696 loops=1)

  • Buffers: shared hit=491,439, temp read=91,963 written=6,890
57. 51.899 61,127.805 ↓ 14,848.0 29,696 1

Nested Loop (cost=0.28..16.66 rows=2 width=2,840) (actual time=4,900.802..61,127.805 rows=29,696 loops=1)

  • Buffers: shared hit=491,439, temp read=91,963 written=5,295
58. 60,957.122 60,957.122 ↓ 14,848.0 29,696 1

CTE Scan on result result (cost=0..0.04 rows=2 width=2,766) (actual time=4,900.782..60,957.122 rows=29,696 loops=1)

  • Buffers: shared hit=402,351, temp read=91,963 written=5,295
59. 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
60.          

CTE output

61. 36.340 61,490.853 ↑ 1.0 2 1

Aggregate (cost=0.05..0.38 rows=2 width=2,516) (actual time=61,458.134..61,490.853 rows=2 loops=1)

  • Buffers: shared hit=491,442, temp read=94,168 written=8,867
62. 189.588 61,454.513 ↓ 14,848.0 29,696 1

Sort (cost=0.05..0.06 rows=2 width=2,252) (actual time=61,439.788..61,454.513 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=491,442, temp read=94,168 written=8,867
63. 61,264.925 61,264.925 ↓ 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=61,192.631..61,264.925 rows=29,696 loops=1)

  • Buffers: shared hit=491,442, temp read=93,563 written=8,262
Planning time : 7.205 ms
Execution time : 61,499.694 ms