explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QTv3

Settings
# exclusive inclusive rows x rows loops node
1. 1.790 101,495.744 ↓ 14.6 510 1

Sort (cost=890,560.07..890,560.15 rows=35 width=923) (actual time=101,495.650..101,495.744 rows=510 loops=1)

  • Sort Key: (COALESCE(a.applications, '0'::numeric)) DESC, a.views DESC
  • Sort Method: quicksort Memory: 161kB
2.          

CTE view_rate

3. 20.366 548.751 ↑ 1.3 508 1

HashAggregate (cost=86,628.27..86,644.39 rows=645 width=38) (actual time=548.354..548.751 rows=508 loops=1)

  • Group Key: CASE WHEN ((ctt_1.name)::text = 'ontame_advertising'::text) THEN '-1'::integer WHEN ((ctt_1.name)::text = 'employee_advocacy'::text) THEN '-2'::integer ELSE x.d_origin_id END, x.media_id
4.          

CTE view_job_dates

5. 41.563 367.389 ↓ 19.8 42,574 1

WindowAgg (cost=83,313.48..83,631.75 rows=2,147 width=90) (actual time=184.534..367.389 rows=42,574 loops=1)

6. 49.335 325.826 ↓ 19.8 42,574 1

WindowAgg (cost=83,313.48..83,572.71 rows=2,147 width=86) (actual time=184.528..325.826 rows=42,574 loops=1)

7. 29.530 276.491 ↓ 19.8 42,574 1

Finalize GroupAggregate (cost=83,313.48..83,524.40 rows=2,147 width=22) (actual time=184.501..276.491 rows=42,574 loops=1)

  • Group Key: s.d_job_id, s.media_id, s.d_origin_id, s.dt
8. 28.894 246.961 ↓ 33.7 42,574 1

Gather Merge (cost=83,313.48..83,487.15 rows=1,263 width=22) (actual time=184.492..246.961 rows=42,574 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
9. 28.579 218.067 ↓ 16.9 21,287 2 / 2

Partial GroupAggregate (cost=82,313.47..82,345.05 rows=1,263 width=22) (actual time=174.043..218.067 rows=21,287 loops=2)

  • Group Key: s.d_job_id, s.media_id, s.d_origin_id, s.dt
10. 53.469 189.488 ↓ 45.4 57,344 2 / 2

Sort (cost=82,313.47..82,316.63 rows=1,263 width=16) (actual time=174.034..189.488 rows=57,344 loops=2)

  • Sort Key: s.d_job_id, s.media_id, s.d_origin_id, s.dt
  • Sort Method: quicksort Memory: 5205kB
11. 33.860 136.019 ↓ 45.4 57,344 2 / 2

Nested Loop (cost=0.98..82,248.41 rows=1,263 width=16) (actual time=0.111..136.019 rows=57,344 loops=2)

12. 2.619 2.619 ↑ 1.2 8,295 2 / 2

Parallel Index Only Scan using dim_job_customer_id_id_idx on dim_job (cost=0.42..2,104.89 rows=9,621 width=4) (actual time=0.013..2.619 rows=8,295 loops=2)

  • Index Cond: (customer_id = 81)
  • Heap Fetches: 14
13. 99.540 99.540 ↓ 7.0 7 16,590 / 2

Index Scan using fact_lnsi_metrics_customer_id_d_job_id_dt_idx on fact_lnsi_metrics s (cost=0.56..8.32 rows=1 width=16) (actual time=0.003..0.012 rows=7 loops=16,590)

  • Index Cond: ((customer_id = 81) AND (d_job_id = dim_job.id) AND (dt >= '2020-01-04'::date) AND (dt <= '2020-02-02'::date))
  • Filter: (views > 0)
  • Rows Removed by Filter: 1
14. 19.304 528.385 ↓ 19.8 42,574 1

Hash Left Join (cost=2,614.91..2,942.84 rows=2,147 width=50) (actual time=492.690..528.385 rows=42,574 loops=1)

  • Hash Cond: (ca_1.campaign_tracking_type_id = ctt_1.id)
15. 13.759 509.072 ↓ 19.8 42,574 1

Hash Right Join (cost=2,613.85..2,917.43 rows=2,147 width=52) (actual time=492.659..509.072 rows=42,574 loops=1)

  • Hash Cond: (ca_1.id = dor_1.campaign_id)
16. 2.763 2.763 ↑ 1.0 6,456 1

Seq Scan on campaigns ca_1 (cost=0.00..277.69 rows=6,469 width=6) (actual time=0.015..2.763 rows=6,456 loops=1)

17. 18.049 492.550 ↓ 19.8 42,574 1

Hash (cost=2,587.01..2,587.01 rows=2,147 width=54) (actual time=492.550..492.550 rows=42,574 loops=1)

  • Buckets: 65536 (originally 4096) Batches: 1 (originally 1) Memory Usage: 3052kB
18. 27.754 474.501 ↓ 19.8 42,574 1

Hash Left Join (cost=2,514.55..2,587.01 rows=2,147 width=54) (actual time=234.783..474.501 rows=42,574 loops=1)

  • Hash Cond: (x.d_origin_id = dor_1.id)
19. 397.002 397.002 ↓ 19.8 42,574 1

CTE Scan on view_job_dates x (cost=0.00..42.94 rows=2,147 width=50) (actual time=184.538..397.002 rows=42,574 loops=1)

20. 26.165 49.745 ↑ 1.0 83,891 1

Hash (cost=1,465.91..1,465.91 rows=83,891 width=8) (actual time=49.745..49.745 rows=83,891 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 3999kB
21. 23.580 23.580 ↑ 1.0 83,891 1

Seq Scan on dim_origin dor_1 (cost=0.00..1,465.91 rows=83,891 width=8) (actual time=0.013..23.580 rows=83,891 loops=1)

22. 0.004 0.009 ↓ 1.3 4 1

Hash (cost=1.03..1.03 rows=3 width=18) (actual time=0.009..0.009 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.005 0.005 ↓ 1.3 4 1

Seq Scan on campaign_tracking_types ctt_1 (cost=0.00..1.03 rows=3 width=18) (actual time=0.003..0.005 rows=4 loops=1)

24.          

CTE job_origin_agg

25. 23.096 7,985.056 ↓ 6.7 15,864 1

Finalize GroupAggregate (cost=86,206.23..86,507.06 rows=2,373 width=66) (actual time=7,888.313..7,985.056 rows=15,864 loops=1)

  • Group Key: s_1.d_job_id, s_1.media_id, (CASE WHEN ((ctt_2.name)::text = 'ontame_advertising'::text) THEN '-1'::integer WHEN ((ctt_2.name)::text = 'employee_advocacy'::text) THEN '-2'::integer ELSE s_1.d_origin_id END), s_1.d_rating_id, s_1.d_distribution_id
26. 577.161 7,961.960 ↓ 11.4 15,864 1

Gather Merge (cost=86,206.23..86,429.59 rows=1,396 width=66) (actual time=7,888.296..7,961.960 rows=15,864 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
27. 51.432 7,384.799 ↓ 5.7 7,932 2 / 2

Partial GroupAggregate (cost=85,206.22..85,272.53 rows=1,396 width=66) (actual time=7,314.310..7,384.799 rows=7,932 loops=2)

  • Group Key: s_1.d_job_id, s_1.media_id, (CASE WHEN ((ctt_2.name)::text = 'ontame_advertising'::text) THEN '-1'::integer WHEN ((ctt_2.name)::text = 'employee_advocacy'::text) THEN '-2'::integer ELSE s_1.d_origin_id END), s_1.d_rating_id, s_1.d_distribution_id
28. 74.918 7,333.367 ↓ 48.3 67,422 2 / 2

Sort (cost=85,206.22..85,209.71 rows=1,396 width=36) (actual time=7,314.295..7,333.367 rows=67,422 loops=2)

  • Sort Key: s_1.d_job_id, s_1.media_id, (CASE WHEN ((ctt_2.name)::text = 'ontame_advertising'::text) THEN '-1'::integer WHEN ((ctt_2.name)::text = 'employee_advocacy'::text) THEN '-2'::integer ELSE s_1.d_origin_id END), s_1.d_rating_id, s_1.d_distribution_id
  • Sort Method: quicksort Memory: 8658kB
29. 45.476 7,258.449 ↓ 48.3 67,422 2 / 2

Hash Left Join (cost=2,875.15..85,133.30 rows=1,396 width=36) (actual time=94.188..7,258.449 rows=67,422 loops=2)

  • Hash Cond: (ca_2.campaign_tracking_type_id = ctt_2.id)
30. 42.126 7,212.947 ↓ 48.3 67,422 2 / 2

Hash Left Join (cost=2,874.09..85,116.41 rows=1,396 width=38) (actual time=94.121..7,212.947 rows=67,422 loops=2)

  • Hash Cond: (dor_2.campaign_id = ca_2.id)
31. 52.941 7,162.676 ↓ 48.3 67,422 2 / 2

Hash Left Join (cost=2,515.53..84,754.19 rows=1,396 width=40) (actual time=85.911..7,162.676 rows=67,422 loops=2)

  • Hash Cond: (s_1.d_origin_id = dor_2.id)
32. 60.951 7,024.957 ↓ 48.3 67,422 2 / 2

Nested Loop (cost=0.98..82,224.36 rows=1,396 width=36) (actual time=0.558..7,024.957 rows=67,422 loops=2)

33. 4.501 4.501 ↑ 1.2 8,295 2 / 2

Parallel Index Only Scan using dim_job_customer_id_id_idx on dim_job dim_job_1 (cost=0.42..2,104.89 rows=9,621 width=4) (actual time=0.019..4.501 rows=8,295 loops=2)

  • Index Cond: (customer_id = 81)
  • Heap Fetches: 20
34. 6,959.505 6,959.505 ↓ 8.0 8 16,590 / 2

Index Scan using fact_lnsi_metrics_customer_id_d_job_id_dt_idx on fact_lnsi_metrics s_1 (cost=0.56..8.32 rows=1 width=36) (actual time=0.011..0.839 rows=8 loops=16,590)

  • Index Cond: ((customer_id = 81) AND (d_job_id = dim_job_1.id) AND (dt >= '2020-01-04'::date) AND (dt <= '2020-02-02'::date))
35. 43.446 84.778 ↑ 1.0 83,891 2 / 2

Hash (cost=1,465.91..1,465.91 rows=83,891 width=8) (actual time=84.778..84.778 rows=83,891 loops=2)

  • Buckets: 131072 Batches: 1 Memory Usage: 3999kB
36. 41.332 41.332 ↑ 1.0 83,891 2 / 2

Seq Scan on dim_origin dor_2 (cost=0.00..1,465.91 rows=83,891 width=8) (actual time=0.012..41.332 rows=83,891 loops=2)

37. 3.335 8.145 ↑ 1.0 6,456 2 / 2

Hash (cost=277.69..277.69 rows=6,469 width=6) (actual time=8.145..8.145 rows=6,456 loops=2)

  • Buckets: 8192 Batches: 1 Memory Usage: 304kB
38. 4.810 4.810 ↑ 1.0 6,456 2 / 2

Seq Scan on campaigns ca_2 (cost=0.00..277.69 rows=6,469 width=6) (actual time=0.026..4.810 rows=6,456 loops=2)

39. 0.008 0.026 ↓ 1.3 4 2 / 2

Hash (cost=1.03..1.03 rows=3 width=18) (actual time=0.025..0.026 rows=4 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
40. 0.018 0.018 ↓ 1.3 4 2 / 2

Seq Scan on campaign_tracking_types ctt_2 (cost=0.00..1.03 rows=3 width=18) (actual time=0.016..0.018 rows=4 loops=2)

41.          

CTE origin_agg

42. 17.999 8,127.243 ↓ 2.2 510 1

GroupAggregate (cost=15,572.08..17,054.34 rows=237 width=616) (actual time=8,105.330..8,127.243 rows=510 loops=1)

  • Group Key: s_2.media_id, s_2.d_origin_id
43. 17.340 8,109.244 ↓ 1.1 15,864 1

Sort (cost=15,572.08..15,609.75 rows=15,069 width=609) (actual time=8,105.277..8,109.244 rows=15,864 loops=1)

  • Sort Key: s_2.media_id, s_2.d_origin_id
  • Sort Method: quicksort Memory: 2615kB
44. 12.124 8,091.904 ↓ 1.1 15,864 1

Hash Left Join (cost=50.57..14,526.34 rows=15,069 width=609) (actual time=7,890.698..8,091.904 rows=15,864 loops=1)

  • Hash Cond: (s_2.d_distribution_id = ddis.id)
45. 11.683 8,079.769 ↓ 6.7 15,864 1

Hash Left Join (cost=11.99..13,954.43 rows=2,373 width=579) (actual time=7,890.672..8,079.769 rows=15,864 loops=1)

  • Hash Cond: (s_2.d_rating_id = dr.id)
46. 19.466 8,068.065 ↓ 6.7 15,864 1

Nested Loop (cost=0.42..13,925.65 rows=2,373 width=67) (actual time=7,890.624..8,068.065 rows=15,864 loops=1)

47. 8,001.007 8,001.007 ↓ 6.7 15,864 1

CTE Scan on job_origin_agg s_2 (cost=0.00..47.46 rows=2,373 width=66) (actual time=7,888.316..8,001.007 rows=15,864 loops=1)

48. 47.592 47.592 ↑ 1.0 1 15,864

Index Scan using jobs_pkey on jobs j (cost=0.42..5.85 rows=1 width=5) (actual time=0.003..0.003 rows=1 loops=15,864)

  • Index Cond: (id = s_2.d_job_id)
49. 0.010 0.021 ↑ 17.5 4 1

Hash (cost=10.70..10.70 rows=70 width=520) (actual time=0.021..0.021 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
50. 0.011 0.011 ↑ 17.5 4 1

Seq Scan on dim_rating dr (cost=0.00..10.70 rows=70 width=520) (actual time=0.008..0.011 rows=4 loops=1)

51. 0.005 0.011 ↑ 423.3 3 1

Hash (cost=22.70..22.70 rows=1,270 width=36) (actual time=0.010..0.011 rows=3 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
52. 0.006 0.006 ↑ 423.3 3 1

Seq Scan on dim_distribution ddis (cost=0.00..22.70 rows=1,270 width=36) (actual time=0.004..0.006 rows=3 loops=1)

53.          

CTE job_origin_backlog_agg

54. 110.793 92,535.199 ↓ 1.9 116,699 1

Finalize GroupAggregate (cost=659,466.39..666,630.88 rows=62,300 width=44) (actual time=91,224.750..92,535.199 rows=116,699 loops=1)

  • Group Key: s_3.d_job_id, s_3.media_id, s_3.d_distribution_id, (CASE WHEN ((ctt_3.name)::text = 'ontame_advertising'::text) THEN '-1'::integer WHEN ((ctt_3.name)::text = 'employee_advocacy'::text) THEN '-2'::integer ELSE s_3.d_origin_id END)
55. 299.826 92,424.406 ↓ 3.2 116,699 1

Gather Merge (cost=659,466.39..664,963.44 rows=36,647 width=44) (actual time=91,224.739..92,424.406 rows=116,699 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
56. 550.705 92,124.580 ↓ 1.6 58,350 2 / 2

Partial GroupAggregate (cost=658,466.38..659,840.65 rows=36,647 width=44) (actual time=91,210.388..92,124.580 rows=58,350 loops=2)

  • Group Key: s_3.d_job_id, s_3.media_id, s_3.d_distribution_id, (CASE WHEN ((ctt_3.name)::text = 'ontame_advertising'::text) THEN '-1'::integer WHEN ((ctt_3.name)::text = 'employee_advocacy'::text) THEN '-2'::integer ELSE s_3.d_origin_id END)
57. 1,401.475 91,573.875 ↓ 32.4 1,186,472 2 / 2

Sort (cost=658,466.38..658,558.00 rows=36,647 width=26) (actual time=91,210.349..91,573.875 rows=1,186,472 loops=2)

  • Sort Key: s_3.d_job_id, s_3.media_id, s_3.d_distribution_id, (CASE WHEN ((ctt_3.name)::text = 'ontame_advertising'::text) THEN '-1'::integer WHEN ((ctt_3.name)::text = 'employee_advocacy'::text) THEN '-2'::integer ELSE s_3.d_origin_id END)
  • Sort Method: external merge Disk: 43608kB
58. 780.050 90,172.400 ↓ 32.4 1,186,472 2 / 2

Hash Left Join (cost=2,916.12..655,688.28 rows=36,647 width=26) (actual time=75.929..90,172.400 rows=1,186,472 loops=2)

  • Hash Cond: (dor_3.campaign_id = ca_3.id)
59. 890.161 89,382.858 ↓ 32.4 1,186,472 2 / 2

Hash Left Join (cost=2,515.53..654,939.00 rows=36,647 width=30) (actual time=66.384..89,382.858 rows=1,186,472 loops=2)

  • Hash Cond: (s_3.d_origin_id = dor_3.id)
60. 960.360 88,428.107 ↓ 32.4 1,186,472 2 / 2

Nested Loop (cost=0.98..652,023.15 rows=36,647 width=26) (actual time=1.275..88,428.107 rows=1,186,472 loops=2)

61. 5.267 5.267 ↑ 1.2 8,295 2 / 2

Parallel Index Only Scan using dim_job_customer_id_id_idx on dim_job dim_job_2 (cost=0.42..2,104.89 rows=9,621 width=4) (actual time=0.015..5.267 rows=8,295 loops=2)

  • Index Cond: (customer_id = 81)
  • Heap Fetches: 19
62. 87,462.480 87,462.480 ↓ 7.2 143 16,590 / 2

Index Scan using fact_lnsi_metrics_customer_id_d_job_id_dt_idx on fact_lnsi_metrics s_3 (cost=0.56..67.35 rows=20 width=26) (actual time=0.087..10.544 rows=143 loops=16,590)

  • Index Cond: ((customer_id = 81) AND (d_job_id = dim_job_2.id) AND (dt >= '2018-08-01'::date) AND (dt <= '2020-02-02'::date))
63. 34.469 64.590 ↑ 1.0 83,891 2 / 2

Hash (cost=1,465.91..1,465.91 rows=83,891 width=8) (actual time=64.590..64.590 rows=83,891 loops=2)

  • Buckets: 131072 Batches: 1 Memory Usage: 3999kB
64. 30.121 30.121 ↑ 1.0 83,891 2 / 2

Seq Scan on dim_origin dor_3 (cost=0.00..1,465.91 rows=83,891 width=8) (actual time=0.011..30.121 rows=83,891 loops=2)

65. 2.433 9.492 ↑ 1.0 6,456 2 / 2

Hash (cost=319.72..319.72 rows=6,469 width=20) (actual time=9.492..9.492 rows=6,456 loops=2)

  • Buckets: 8192 Batches: 1 Memory Usage: 405kB
66. 3.894 7.059 ↑ 1.0 6,456 2 / 2

Hash Left Join (cost=1.07..319.72 rows=6,469 width=20) (actual time=0.072..7.059 rows=6,456 loops=2)

  • Hash Cond: (ca_3.campaign_tracking_type_id = ctt_3.id)
67. 3.148 3.148 ↑ 1.0 6,456 2 / 2

Seq Scan on campaigns ca_3 (cost=0.00..277.69 rows=6,469 width=6) (actual time=0.022..3.148 rows=6,456 loops=2)

68. 0.006 0.017 ↓ 1.3 4 2 / 2

Hash (cost=1.03..1.03 rows=3 width=18) (actual time=0.017..0.017 rows=4 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
69. 0.011 0.011 ↓ 1.3 4 2 / 2

Seq Scan on campaign_tracking_types ctt_3 (cost=0.00..1.03 rows=3 width=18) (actual time=0.009..0.011 rows=4 loops=2)

70.          

CTE origin_backlog_agg

71. 16.047 92,746.694 ↑ 6.3 990 1

HashAggregate (cost=30,264.97..30,467.44 rows=6,230 width=302) (actual time=92,745.905..92,746.694 rows=990 loops=1)

  • Group Key: s_4.media_id, s_4.d_origin_id
72. 13.738 92,730.647 ↑ 13.7 28,843 1

Merge Right Join (cost=6,522.32..12,462.74 rows=395,605 width=70) (actual time=92,711.096..92,730.647 rows=28,843 loops=1)

  • Merge Cond: (ddis_1.id = s_4.d_distribution_id)
73. 0.007 0.020 ↑ 423.3 3 1

Sort (cost=88.17..91.35 rows=1,270 width=36) (actual time=0.018..0.020 rows=3 loops=1)

  • Sort Key: ddis_1.id
  • Sort Method: quicksort Memory: 25kB
74. 0.013 0.013 ↑ 423.3 3 1

Seq Scan on dim_distribution ddis_1 (cost=0.00..22.70 rows=1,270 width=36) (actual time=0.011..0.013 rows=3 loops=1)

75. 18.467 92,716.889 ↑ 2.2 28,843 1

Sort (cost=6,434.14..6,589.89 rows=62,300 width=40) (actual time=92,711.068..92,716.889 rows=28,843 loops=1)

  • Sort Key: s_4.d_distribution_id
  • Sort Method: quicksort Memory: 3022kB
76. 52.423 92,698.422 ↑ 2.2 28,843 1

Hash Join (cost=59.89..1,472.90 rows=62,300 width=40) (actual time=91,238.487..92,698.422 rows=28,843 loops=1)

  • Hash Cond: (s_4.d_job_id = job_origin_agg.d_job_id)
77. 92,632.892 92,632.892 ↓ 1.9 116,699 1

CTE Scan on job_origin_backlog_agg s_4 (cost=0.00..1,246.00 rows=62,300 width=44) (actual time=91,224.754..92,632.892 rows=116,699 loops=1)

78. 0.601 13.107 ↓ 11.2 2,245 1

Hash (cost=57.39..57.39 rows=200 width=4) (actual time=13.107..13.107 rows=2,245 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 111kB
79. 6.729 12.506 ↓ 11.2 2,245 1

HashAggregate (cost=53.39..55.39 rows=200 width=4) (actual time=11.853..12.506 rows=2,245 loops=1)

  • Group Key: job_origin_agg.d_job_id
80. 5.777 5.777 ↓ 6.7 15,864 1

CTE Scan on job_origin_agg (cost=0.00..47.46 rows=2,373 width=4) (actual time=0.002..5.777 rows=15,864 loops=1)

81.          

CTE origin_calc

82. 1.228 92,771.738 ↓ 2.2 510 1

Hash Right Join (cost=8.29..555.99 rows=237 width=102) (actual time=92,768.938..92,771.738 rows=510 loops=1)

  • Hash Cond: ((ba.media_id = a_1.media_id) AND (ba.d_origin_id = a_1.d_origin_id))
83. 92,747.512 92,747.512 ↑ 6.3 990 1

CTE Scan on origin_backlog_agg ba (cost=0.00..124.60 rows=6,230 width=78) (actual time=92,745.909..92,747.512 rows=990 loops=1)

84. 0.257 22.998 ↓ 2.2 510 1

Hash (cost=4.74..4.74 rows=237 width=270) (actual time=22.998..22.998 rows=510 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
85. 22.741 22.741 ↓ 2.2 510 1

CTE Scan on origin_agg a_1 (cost=0.00..4.74 rows=237 width=270) (actual time=0.002..22.741 rows=510 loops=1)

86.          

CTE conversion_rate

87. 0.355 0.355 ↓ 2.2 508 1

CTE Scan on origin_agg (cost=0.00..6.50 rows=233 width=38) (actual time=0.006..0.355 rows=508 loops=1)

  • Filter: ((views IS NOT NULL) AND (applications IS NOT NULL) AND (views <> '0'::numeric))
  • Rows Removed by Filter: 2
88.          

CTE application_rate

89. 0.391 1.417 ↓ 507.0 507 1

Hash Join (cost=27.73..45.48 rows=1 width=38) (actual time=0.911..1.417 rows=507 loops=1)

  • Hash Cond: ((vr_1.media_id = cr_1.media_id) AND (vr_1.d_origin_id = cr_1.d_origin_id))
90. 0.134 0.134 ↑ 1.3 508 1

CTE Scan on view_rate vr_1 (cost=0.00..12.90 rows=645 width=38) (actual time=0.001..0.134 rows=508 loops=1)

91. 0.166 0.892 ↓ 507.0 507 1

Hash (cost=27.72..27.72 rows=1 width=44) (actual time=0.892..0.892 rows=507 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
92. 0.291 0.726 ↓ 507.0 507 1

Hash Join (cost=8.15..27.72 rows=1 width=44) (actual time=0.323..0.726 rows=507 loops=1)

  • Hash Cond: ((oa.media_id = cr_1.media_id) AND (oa.d_origin_id = cr_1.d_origin_id))
93. 0.124 0.124 ↓ 2.2 510 1

CTE Scan on origin_agg oa (cost=0.00..4.74 rows=237 width=6) (actual time=0.000..0.124 rows=510 loops=1)

94. 0.168 0.311 ↓ 2.2 507 1

Hash (cost=4.66..4.66 rows=233 width=38) (actual time=0.311..0.311 rows=507 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
95. 0.143 0.143 ↓ 2.2 508 1

CTE Scan on conversion_rate cr_1 (cost=0.00..4.66 rows=233 width=38) (actual time=0.001..0.143 rows=508 loops=1)

96.          

CTE good_candidates_rate

97. 0.185 0.185 ↑ 3.0 78 1

CTE Scan on origin_agg origin_agg_1 (cost=0.00..7.10 rows=235 width=38) (actual time=0.010..0.185 rows=78 loops=1)

  • Filter: ((applications <> '0'::numeric) AND (applications_rank_1 <> '0'::numeric))
  • Rows Removed by Filter: 432
98. 0.421 101,493.954 ↓ 14.6 510 1

Hash Left Join (cost=2,568.46..2,639.98 rows=35 width=923) (actual time=101,485.252..101,493.954 rows=510 loops=1)

  • Hash Cond: ((a.media_id = gcr.media_id) AND (a.d_origin_id = gcr.d_origin_id))
99. 0.383 101,493.268 ↓ 14.6 510 1

Hash Left Join (cost=2,560.23..2,629.03 rows=35 width=871) (actual time=101,484.977..101,493.268 rows=510 loops=1)

  • Hash Cond: ((a.media_id = ar.media_id) AND (a.d_origin_id = ar.d_origin_id))
100. 0.378 101,490.993 ↓ 14.6 510 1

Hash Left Join (cost=2,560.20..2,628.72 rows=35 width=839) (actual time=101,483.069..101,490.993 rows=510 loops=1)

  • Hash Cond: ((a.media_id = cr.media_id) AND (a.d_origin_id = cr.d_origin_id))
101. 0.408 101,489.751 ↓ 14.6 510 1

Hash Left Join (cost=2,552.05..2,618.37 rows=35 width=807) (actual time=101,482.183..101,489.751 rows=510 loops=1)

  • Hash Cond: ((a.media_id = c.media_id) AND (a.d_origin_id = c.d_origin_id))
102. 0.405 8,717.028 ↓ 14.6 510 1

Hash Left Join (cost=2,543.75..2,607.79 rows=35 width=727) (actual time=8,709.850..8,717.028 rows=510 loops=1)

  • Hash Cond: ((a.media_id = vr.media_id) AND (a.d_origin_id = vr.d_origin_id))
103. 0.700 8,167.351 ↓ 14.6 510 1

Nested Loop Left Join (cost=2,521.18..2,579.43 rows=35 width=695) (actual time=8,160.568..8,167.351 rows=510 loops=1)

104. 0.332 8,164.611 ↓ 14.6 510 1

Hash Left Join (cost=2,520.90..2,560.92 rows=35 width=683) (actual time=8,160.555..8,164.611 rows=510 loops=1)

  • Hash Cond: (ca.campaign_tracking_type_id = ctt.id)
  • Filter: ((ca.id IS NULL) OR ((ctt.name)::text = 'customer_campaign'::text))
105. 0.693 8,164.265 ↓ 4.9 510 1

Nested Loop Left Join (cost=2,519.83..2,559.10 rows=105 width=673) (actual time=8,160.524..8,164.265 rows=510 loops=1)

106. 0.960 8,161.532 ↓ 4.9 510 1

Hash Left Join (cost=2,519.55..2,526.37 rows=105 width=630) (actual time=8,160.512..8,161.532 rows=510 loops=1)

  • Hash Cond: (a.d_origin_id = dor.id)
107. 0.393 8,105.945 ↓ 4.9 510 1

Hash Join (cost=5.00..10.38 rows=105 width=606) (actual time=8,105.409..8,105.945 rows=510 loops=1)

  • Hash Cond: (a.customer_id = cu.id)
108. 8,105.490 8,105.490 ↓ 2.2 510 1

CTE Scan on origin_agg a (cost=0.00..4.74 rows=237 width=608) (actual time=8,105.335..8,105.490 rows=510 loops=1)

109. 0.026 0.062 ↑ 1.0 89 1

Hash (cost=3.89..3.89 rows=89 width=4) (actual time=0.062..0.062 rows=89 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
110. 0.036 0.036 ↑ 1.0 89 1

Seq Scan on customers cu (cost=0.00..3.89 rows=89 width=4) (actual time=0.010..0.036 rows=89 loops=1)

111. 31.199 54.627 ↑ 1.0 83,891 1

Hash (cost=1,465.91..1,465.91 rows=83,891 width=28) (actual time=54.627..54.627 rows=83,891 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 5769kB
112. 23.428 23.428 ↑ 1.0 83,891 1

Seq Scan on dim_origin dor (cost=0.00..1,465.91 rows=83,891 width=28) (actual time=0.008..23.428 rows=83,891 loops=1)

113. 2.040 2.040 ↓ 0.0 0 510

Index Scan using campaigns_pkey on campaigns ca (cost=0.28..0.31 rows=1 width=43) (actual time=0.004..0.004 rows=0 loops=510)

  • Index Cond: (id = dor.campaign_id)
114. 0.005 0.014 ↓ 1.3 4 1

Hash (cost=1.03..1.03 rows=3 width=18) (actual time=0.014..0.014 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
115. 0.009 0.009 ↓ 1.3 4 1

Seq Scan on campaign_tracking_types ctt (cost=0.00..1.03 rows=3 width=18) (actual time=0.007..0.009 rows=4 loops=1)

116. 2.040 2.040 ↑ 1.0 1 510

Index Scan using media_pkey on media m (cost=0.28..0.53 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=510)

  • Index Cond: (id = a.media_id)
117. 0.200 549.272 ↑ 1.3 507 1

Hash (cost=12.90..12.90 rows=645 width=38) (actual time=549.272..549.272 rows=507 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
118. 549.072 549.072 ↑ 1.3 508 1

CTE Scan on view_rate vr (cost=0.00..12.90 rows=645 width=38) (actual time=548.359..549.072 rows=508 loops=1)

119. 0.194 92,772.315 ↓ 2.1 508 1

Hash (cost=4.74..4.74 rows=237 width=86) (actual time=92,772.315..92,772.315 rows=508 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
120. 92,772.121 92,772.121 ↓ 2.2 510 1

CTE Scan on origin_calc c (cost=0.00..4.74 rows=237 width=86) (actual time=92,768.941..92,772.121 rows=510 loops=1)

121. 0.176 0.864 ↓ 2.2 507 1

Hash (cost=4.66..4.66 rows=233 width=38) (actual time=0.863..0.864 rows=507 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
122. 0.688 0.688 ↓ 2.2 508 1

CTE Scan on conversion_rate cr (cost=0.00..4.66 rows=233 width=38) (actual time=0.009..0.688 rows=508 loops=1)

123. 0.164 1.892 ↓ 507.0 507 1

Hash (cost=0.02..0.02 rows=1 width=38) (actual time=1.892..1.892 rows=507 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
124. 1.728 1.728 ↓ 507.0 507 1

CTE Scan on application_rate ar (cost=0.00..0.02 rows=1 width=38) (actual time=0.913..1.728 rows=507 loops=1)

125. 0.031 0.265 ↑ 3.1 76 1

Hash (cost=4.70..4.70 rows=235 width=38) (actual time=0.265..0.265 rows=76 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
126. 0.234 0.234 ↑ 3.0 78 1

CTE Scan on good_candidates_rate gcr (cost=0.00..4.70 rows=235 width=38) (actual time=0.012..0.234 rows=78 loops=1)

Planning time : 6.249 ms
Execution time : 101,514.371 ms