explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9hcx

Settings
# exclusive inclusive rows x rows loops node
1. 59,464.201 59,464.201 ↓ 2.0 2 1

CTE Scan on output output (cost=472,370.95..472,371 rows=1 width=2,480) (actual time=59,442.666..59,464.201 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. 180.956 4,560.397 ↓ 14,847.5 29,695 1

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

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

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

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

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

Nested Loop (cost=0.84..344,918.78 rows=2 width=262) (actual time=328.993..4,063.599 rows=29,695 loops=1)

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

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

  • Buffers: shared hit=108,521, temp read=5,685 written=378
8. 2.779 2.779 ↓ 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.06..2.779 rows=16 loops=1)

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

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

  • Buffers: shared hit=103,915, temp read=5,685 written=378
10. 263.842 263.842 ↓ 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.284..263.842 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,245.782 1,247.190 ↓ 1.8 1,053 29,695

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

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

Seq Scan on campaign_assignment campaign_assignment (cost=0..331.39 rows=586 width=42) (actual time=0.015..1.408 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.154 68.265 ↓ 2.0 2 1

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

  • Buffers: shared hit=26,404
15. 0.012 65.699 ↓ 2.0 2 1

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

  • Buffers: shared hit=26,078
16. 0.005 65.669 ↓ 2.0 2 1

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

  • Buffers: shared hit=26,074
17. 0.011 65.656 ↓ 2.0 2 1

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

  • Buffers: shared hit=26,066
18. 65.579 65.579 ↑ 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=51.42..65.579 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.023 0.066 ↓ 2.3 16 2

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

  • Buffers: shared hit=15
20. 0.020 0.043 ↓ 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.028..0.043 rows=16 loops=1)

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

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

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

Index Scan using pixel_pkey on pixel pixel (cost=0.42..6.46 rows=1 width=27) (actual time=0.004..0.004 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.004..0.009 rows=75 loops=2)

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

Seq Scan on campaign_assignment campaign_assignment_1 (cost=0..331.39 rows=586 width=42) (actual time=0.007..1.206 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.034 68.307 ↑ 1.0 1 1

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

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

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

  • Buffers: shared hit=26,404
28.          

CTE goals

29. 4.791 205.618 ↓ 8,484.4 59,391 1

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

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

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

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

CTE Scan on gen_met gen_met (cost=0..0.05 rows=2 width=318) (actual time=16.373..135.042 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.604 25.604 ↓ 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.023..25.604 rows=19,032 loops=1)

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

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

  • Filter: ((gen_met_2.channel_bench_id)::text = 'kMky6w52aQGZEeb6s'::text)
  • Buffers: temp read=1,016
34. 0.003 0.011 ↑ 1.0 1 1

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

35. 0.008 0.008 ↑ 1.0 1 1

CTE Scan on conv_met conv_met_1 (cost=0..0.04 rows=1 width=318) (actual time=0.008..0.008 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.689 27.415 ↓ 14,847.5 29,695 1

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

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

  • Buffers: temp read=1,016
38.          

CTE media_type_goals

39. 10.103 477.449 ↓ 10,664.0 10,664 1

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

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

Sort (cost=58.39..58.39 rows=1 width=204) (actual time=466.352..467.346 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. 17.990 453.525 ↓ 10,664.0 10,664 1

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

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

CTE Scan on goals goals (cost=0..0.14 rows=7 width=402) (actual time=16.38..257.362 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. 32,428.829 58,998.037 ↓ 29,696.0 29,696 1

Nested Loop (cost=0.33..16.89 rows=1 width=757) (actual time=5,011.421..58,998.037 rows=29,696 loops=1)

  • Filter: ((COALESCE(gen_met_4.date, conv_met_gen_met_lvl.date, media_type_goals.date) >= '2020-08-02'::date) AND (COALESCE(gen_met_4.date, conv_met_gen_met_lvl.date, media_type_goals.date) <= '2020-09-01'::date))
  • Buffers: shared hit=402,351, temp read=91,963 written=3,967
46. 34.534 4,623.864 ↓ 14,848.0 29,696 1

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

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

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

  • Buffers: shared hit=135,089, temp read=73,368 written=1,488
48. 4,471.250 4,471.250 ↓ 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,460.596..4,471.25 rows=29,695 loops=1)

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

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

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

  • Buffers: shared hit=26,404
51. 29.696 29.696 ↑ 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.001 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. 21,945.344 21,945.344 ↓ 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.739 rows=10,664 loops=29,696)

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

CTE result_goal_names

54. 30.307 59,245.501 ↓ 29,696.0 29,696 1

Hash Join (cost=8.35..13.49 rows=1 width=2,832) (actual time=59,223.34..59,245.501 rows=29,696 loops=1)

  • Buffers: shared hit=491,442, temp read=93,563 written=6,893
55. 0.024 0.024 ↑ 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.024 rows=155 loops=1)

  • Filter: (campaign_goal_mapping.is_archived IS NOT TRUE)
  • Buffers: shared hit=3
56. 38.828 59,215.170 ↓ 29,696.0 29,696 1

Hash (cost=8.34..8.34 rows=1 width=2,840) (actual time=59,215.17..59,215.17 rows=29,696 loops=1)

  • Buffers: shared hit=491,439, temp read=91,963 written=6,890
57. 35.722 59,176.342 ↓ 29,696.0 29,696 1

Nested Loop (cost=0.28..8.34 rows=1 width=2,840) (actual time=5,011.443..59,176.342 rows=29,696 loops=1)

  • Buffers: shared hit=491,439, temp read=91,963 written=5,295
58. 59,051.532 59,051.532 ↓ 29,696.0 29,696 1

CTE Scan on result result (cost=0..0.03 rows=1 width=2,766) (actual time=5,011.425..59,051.532 rows=29,696 loops=1)

  • Filter: ((result.date >= '2020-08-02'::date) AND (result.date <= '2020-09-01'::date))
  • Buffers: shared hit=402,351, temp read=91,963 written=5,295
59. 89.088 89.088 ↑ 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.003..0.003 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. 27.191 59,464.193 ↓ 2.0 2 1

Aggregate (cost=0.03..0.2 rows=1 width=2,516) (actual time=59,442.66..59,464.193 rows=2 loops=1)

  • Buffers: shared hit=491,442, temp read=94,168 written=8,867
62. 153.164 59,437.002 ↓ 29,696.0 29,696 1

Sort (cost=0.03..0.04 rows=1 width=2,252) (actual time=59,424.804..59,437.002 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. 59,283.838 59,283.838 ↓ 29,696.0 29,696 1

CTE Scan on result_goal_names result_goal_names (cost=0..0.02 rows=1 width=2,252) (actual time=59,223.345..59,283.838 rows=29,696 loops=1)

  • Buffers: shared hit=491,442, temp read=93,563 written=8,262
Planning time : 5.616 ms
Execution time : 59,470.48 ms