explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mPFO

Settings
# exclusive inclusive rows x rows loops node
1. 0.032 40,807.347 ↓ 50.0 50 1

Limit (cost=8,838.73..8,839.27 rows=1 width=88) (actual time=40,807.184..40,807.347 rows=50 loops=1)

  • Buffers: shared hit=9935, temp read=584778 written=1138
2. 0.594 40,807.315 ↓ 10.5 200 1

Result (cost=8,828.62..8,838.73 rows=19 width=88) (actual time=40,806.741..40,807.315 rows=200 loops=1)

  • Buffers: shared hit=9935, temp read=584778 written=1138
3. 4.123 40,806.721 ↓ 10.5 200 1

Sort (cost=8,828.62..8,828.66 rows=19 width=632) (actual time=40,806.702..40,806.721 rows=200 loops=1)

  • Sort Key: c_media_plan_adsets.starts_at DESC
  • Sort Method: top-N heapsort Memory: 123kB
  • Buffers: shared hit=9935, temp read=584778 written=1138
4. 327.077 40,802.598 ↓ 39.4 749 1

Nested Loop (cost=6,333.27..8,828.21 rows=19 width=632) (actual time=411.636..40,802.598 rows=749 loops=1)

  • Buffers: shared hit=9935, temp read=584778 written=1138
5. 9.602 134.011 ↓ 1,034.0 1,034 1

Nested Loop (cost=10.52..285.34 rows=1 width=2,029) (actual time=0.215..134.011 rows=1,034 loops=1)

  • Buffers: shared hit=8115
6. 5.071 117.171 ↓ 1,034.0 1,034 1

Nested Loop (cost=10.37..284.82 rows=1 width=267) (actual time=0.201..117.171 rows=1,034 loops=1)

  • Buffers: shared hit=6047
7. 6.881 106.930 ↓ 1,034.0 1,034 1

Nested Loop (cost=10.23..284.48 rows=1 width=267) (actual time=0.194..106.93 rows=1,034 loops=1)

  • Buffers: shared hit=3979
8. 15.292 87.641 ↓ 1,034.0 1,034 1

Nested Loop (cost=10.08..284.13 rows=1 width=53) (actual time=0.185..87.641 rows=1,034 loops=1)

  • Buffers: shared hit=1911
9. 2.048 60.975 ↓ 1,034.0 1,034 1

Nested Loop (cost=10.08..280.65 rows=1 width=32) (actual time=0.175..60.975 rows=1,034 loops=1)

  • Buffers: shared hit=758
10. 0.285 1.199 ↓ 1.6 88 1

Hash Join (cost=9.81..17.38 rows=54 width=4) (actual time=0.134..1.199 rows=88 loops=1)

  • Buffers: shared hit=11
11. 0.111 0.881 ↓ 1.6 88 1

Nested Loop (cost=7.86..15.28 rows=54 width=8) (actual time=0.093..0.881 rows=88 loops=1)

  • Buffers: shared hit=10
12. 0.015 0.015 ↑ 1.0 1 1

Index Only Scan using c_clients_pkey on c_clients c_clients (cost=0.15..2.17 rows=1 width=4) (actual time=0.012..0.015 rows=1 loops=1)

  • Index Cond: (c_clients.id = 9)
  • Buffers: shared hit=2
13. 0.454 0.755 ↓ 1.6 88 1

Hash Join (cost=7.71..12.58 rows=54 width=12) (actual time=0.08..0.755 rows=88 loops=1)

  • Buffers: shared hit=8
14. 0.235 0.235 ↓ 1.1 158 1

Seq Scan on c_flights c_flights (cost=0..4.47 rows=147 width=12) (actual time=0.007..0.235 rows=158 loops=1)

  • Buffers: shared hit=3
15. 0.016 0.066 ↓ 1.0 60 1

Hash (cost=6.99..6.99 rows=58 width=28) (actual time=0.065..0.066 rows=60 loops=1)

  • Buffers: shared hit=5
16. 0.050 0.050 ↓ 1.0 60 1

Seq Scan on c_campaigns c_campaigns (cost=0..6.99 rows=58 width=28) (actual time=0.009..0.05 rows=60 loops=1)

  • Filter: (c_campaigns.client_id = 9)
  • Buffers: shared hit=5
17. 0.011 0.033 ↓ 1.1 46 1

Hash (cost=1.42..1.42 rows=42 width=4) (actual time=0.033..0.033 rows=46 loops=1)

  • Buffers: shared hit=1
18. 0.022 0.022 ↓ 1.1 46 1

Seq Scan on c_business_lines c_business_lines (cost=0..1.42 rows=42 width=4) (actual time=0.015..0.022 rows=46 loops=1)

  • Buffers: shared hit=1
19. 57.728 57.728 ↓ 12.0 12 88

Index Scan using c_media_plan_adsets_flight_id_index on c_media_plan_adsets c_media_plan_adsets (cost=0.28..4.87 rows=1 width=36) (actual time=0.05..0.656 rows=12 loops=88)

  • Index Cond: (c_media_plan_adsets.flight_id = c_flights.id)
  • Filter: (((((c_media_plan_adsets.ends_at - c_media_plan_adsets.starts_at) + 1) - CASE WHEN ((((now())::date - c_media_plan_adsets.starts_at) + 1) < 0) THEN 0 ELSE ((now())::date - c_media_plan_adsets.starts_at) END) < 5) AND ((get_media_plan_adset_status(c_media_plan_adsets.is_ended, c_media_plan_adsets.is_paused, c_media_plan_adsets.is_connected, c_media_plan_adsets.ends_at))::text = 'ENDED'::text))
  • Buffers: shared hit=747
20. 11.374 11.374 ↑ 3.0 22 1,034

Seq Scan on c_channels c_channels (cost=0..2.66 rows=66 width=37) (actual time=0.009..0.011 rows=22 loops=1,034)

  • Buffers: shared hit=1153
21. 12.408 12.408 ↑ 1.0 1 1,034

Index Scan using c_metrics_pkey on c_metrics c_metrics (cost=0.14..0.34 rows=1 width=222) (actual time=0.012..0.012 rows=1 loops=1,034)

  • Index Cond: (c_metrics.id = c_channels.metric_id)
  • Buffers: shared hit=2068
22. 5.170 5.170 ↑ 1.0 1 1,034

Index Scan using c_suppliers_pkey on c_suppliers c_suppliers (cost=0.14..0.34 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1,034)

  • Index Cond: (c_suppliers.id = c_channels.supplier_id)
  • Buffers: shared hit=2068
23. 7.238 7.238 ↑ 1.0 1 1,034

Index Scan using ca_vendors_pkey on c_vendors c_vendors (cost=0.14..0.24 rows=1 width=222) (actual time=0.007..0.007 rows=1 loops=1,034)

  • Index Cond: (c_vendors.id = c_suppliers.vendor_id)
  • Buffers: shared hit=2068
24. 31,907.172 40,341.510 ↑ 1.8 2,144 1,034

Aggregate (cost=6,322.75..8,456.97 rows=3,788 width=235) (actual time=0.16..39.015 rows=2,144 loops=1,034)

  • Buffers: shared hit=1820, temp read=583745 written=1138
25. 8,402.383 8,434.338 ↑ 1.1 32,258 1,034

Sort (cost=6,322.75..6,410.1 rows=34,939 width=127) (actual time=0.149..8.157 rows=32,258 loops=1,034)

  • Sort Key: insights_consolidated.account_name, insights_consolidated.source_platform_adset_id, insights_consolidated.media_plan_adset_id
  • Sort Method: external sort Disk: 4544kB
  • Buffers: shared hit=1820, temp read=583745 written=1138
26. 31.955 31.955 ↑ 1.1 32,258 1

Seq Scan on insights_consolidated insights_consolidated (cost=0..2,388.15 rows=34,939 width=127) (actual time=0.011..31.955 rows=32,258 loops=1)

  • Filter: ((insights_consolidated.captured_at >= '2017-01-01'::date) AND (insights_consolidated.captured_at <= '2019-08-01'::date))
  • Buffers: shared hit=1820