explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 56,880.784 56,880.784 ↑ 1.0 2 1

CTE Scan on output output (cost=413.31..413.41 rows=2 width=2,480) (actual time=56,858.212..56,880.784 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=441,847 read=125, temp read=6,008 written=7,234
2.          

CTE gen_met

3. 12.728 78.781 ↓ 14,847.5 29,695 1

Nested Loop (cost=5.04..154.5 rows=2 width=261) (actual time=0.055..78.781 rows=29,695 loops=1)

  • Buffers: shared hit=85,460 read=119
4. 9.454 36.358 ↓ 14,847.5 29,695 1

Nested Loop (cost=4.9..154.13 rows=2 width=220) (actual time=0.051..36.358 rows=29,695 loops=1)

  • Buffers: shared hit=26,070 read=119
5. 0.015 0.024 ↓ 2.3 16 1

Bitmap Heap Scan on source_campaign source_campaign_ids (cost=4.34..29.92 rows=7 width=141) (actual time=0.011..0.024 rows=16 loops=1)

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

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

  • Index Cond: ((source_campaign_ids.advertiser_bench_id)::text = '9sso6JNx9HkfiXynC'::text)
  • Buffers: shared hit=2
7. 26.880 26.880 ↓ 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.005..1.68 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,055 read=119
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.17 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.006 0.176 ↓ 2.0 2 1

Nested Loop (cost=5.46..160.81 rows=1 width=281) (actual time=0.12..0.176 rows=2 loops=1)

  • Buffers: shared hit=35 read=6
11. 0.002 0.166 ↓ 2.0 2 1

Nested Loop (cost=5.31..160.61 rows=1 width=248) (actual time=0.114..0.166 rows=2 loops=1)

  • Buffers: shared hit=31 read=6
12. 0.019 0.150 ↓ 2.0 2 1

Nested Loop (cost=4.9..154.13 rows=1 width=221) (actual time=0.103..0.15 rows=2 loops=1)

  • Buffers: shared hit=23 read=6
13. 0.026 0.051 ↓ 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.032..0.051 rows=16 loops=1)

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

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

  • Index Cond: ((source_campaign_ids_1.advertiser_bench_id)::text = '9sso6JNx9HkfiXynC'::text)
  • Buffers: shared hit=2
15. 0.080 0.080 ↓ 0.0 0 16

Index Scan using conv_met_test on conversion conversion_metrics (cost=0.56..17.73 rows=1 width=80) (actual time=0.005..0.005 rows=0 loops=16)

  • Index Cond: ((conversion_metrics.source_campaign_id = source_campaign_ids_1.id) AND (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=8 read=6
16. 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
17. 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.19 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
18.          

CTE conv_met_gen_met_lvl

19. 0.017 0.196 ↑ 1.0 1 1

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

  • Buffers: shared hit=35 read=6
20. 0.179 0.179 ↓ 2.0 2 1

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

  • Buffers: shared hit=35 read=6
21.          

CTE goals

22. 5.396 193.665 ↓ 8,484.4 59,391 1

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

  • Buffers: shared hit=85,445 read=117, temp read=2,874 written=957
23. 5.607 123.641 ↓ 5,331.5 10,663 1

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

  • Buffers: shared hit=85,445 read=117, temp written=956
24. 118.034 118.034 ↓ 5,331.5 10,663 1

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

  • Filter: ((gen_met.channel_bench_id)::text = ANY ('{N3GohqX5pWh4KRh5N,xCXNXwG98SFjqubNE,hJQDHf369Z6P7mFqg}'::text[]))
  • Buffers: shared hit=85,445 read=117, temp written=956
25. 30.811 30.811 ↓ 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.018..30.811 rows=19,032 loops=1)

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

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

  • Filter: ((gen_met_2.channel_bench_id)::text = 'kMky6w52aQGZEeb6s'::text)
  • Buffers: temp read=958
27. 0.005 0.013 ↑ 1.0 1 1

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

28. 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.007..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)))
29. 12.728 26.808 ↓ 14,847.5 29,695 1

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

  • Buffers: temp read=958
30. 14.080 14.080 ↓ 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.08 rows=29,695 loops=1)

  • Buffers: temp read=958
31.          

CTE media_type_goals

32. 10.203 492.417 ↓ 10,664.0 10,664 1

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

  • Buffers: shared hit=263,618 read=117, temp read=2,874 written=2,421
33. 14.261 482.214 ↓ 10,664.0 10,664 1

Sort (cost=58.39..58.39 rows=1 width=204) (actual time=481.109..482.214 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=263,618 read=117, temp read=2,874 written=2,421
34. 38.180 467.953 ↓ 10,664.0 10,664 1

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

  • Buffers: shared hit=263,618 read=117, temp read=2,874 written=2,421
35. 251.600 251.600 ↓ 8,484.4 59,391 1

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

  • Buffers: shared hit=85,445 read=117, temp read=2,874 written=2,421
36. 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
37.          

CTE result

38. 33,455.056 56,330.154 ↓ 14,848.0 29,696 1

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

  • Buffers: shared hit=352,756 read=125, temp read=3,832 written=2,421
39. 26.460 127.962 ↓ 14,848.0 29,696 1

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

  • Buffers: shared hit=89,138 read=8, temp read=958
40. 26.435 42.110 ↓ 14,848.0 29,696 1

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

  • Buffers: shared hit=50 read=8, temp read=958
41. 15.471 15.471 ↓ 14,847.5 29,695 1

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

  • Buffers: shared hit=15 read=2, temp read=958
42. 0.005 0.204 ↑ 1.0 1 1

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

  • Buffers: shared hit=35 read=6
43. 0.199 0.199 ↑ 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.198..0.199 rows=1 loops=1)

  • Buffers: shared hit=35 read=6
44. 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
45. 22,747.136 22,747.136 ↓ 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.766 rows=10,664 loops=29,696)

  • Buffers: shared hit=263,618 read=117, temp read=2,874 written=2,421
46.          

CTE result_goal_names

47. 31.830 56,655.352 ↓ 14,848.0 29,696 1

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

  • Buffers: shared hit=441,847 read=125, temp read=5,403 written=5,289
48. 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.006..0.021 rows=155 loops=1)

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

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

  • Buffers: shared hit=441,844 read=125, temp read=3,832 written=5,286
50. 59.537 56,569.900 ↓ 14,848.0 29,696 1

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

  • Buffers: shared hit=441,844 read=125, temp read=3,832 written=3,720
51. 56,391.579 56,391.579 ↓ 14,848.0 29,696 1

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

  • Buffers: shared hit=352,756 read=125, temp read=3,832 written=3,720
52. 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
53.          

CTE output

54. 27.996 56,880.775 ↑ 1.0 2 1

Aggregate (cost=0.05..0.38 rows=2 width=2,516) (actual time=56,858.206..56,880.775 rows=2 loops=1)

  • Buffers: shared hit=441,847 read=125, temp read=6,008 written=7,234
55. 156.435 56,852.779 ↓ 14,848.0 29,696 1

Sort (cost=0.05..0.06 rows=2 width=2,252) (actual time=56,846.081..56,852.779 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=441,847 read=125, temp read=6,008 written=7,234
56. 56,696.344 56,696.344 ↓ 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,631.711..56,696.344 rows=29,696 loops=1)

  • Buffers: shared hit=441,847 read=125, temp read=5,403 written=6,629
Planning time : 3.901 ms
Execution time : 56,886.355 ms