explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IBQO

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 360.654 ↑ 72,371,428.6 7 1

GroupAggregate (cost=1,594,212,165.85..1,693,744,731.41 rows=506,600,000 width=48) (actual time=360.589..360.654 rows=7 loops=1)

  • Group Key: campaigns.id, (CASE WHEN ((max(ad_groups.id)) IS NULL) THEN 'incomplete_ad_group'::character varying WHEN ((max(ad_group_bid_skus.id)) IS NULL) THEN 'incomplete_ad_group_products'::character varying ELSE campaigns_1.workflow_state END), (CASE WHEN ((max(ad_group_bid_skus.id)) IS NULL) THEN 0 WHEN ((max(ad_groups.id)) IS NULL) THEN 0 WHEN ((campaigns_1.workflow_state)::text = 'ended'::text) THEN 1 WHEN ((campaigns_1.workflow_state)::text = 'out_of_budget'::text) THEN 2 WHEN ((campaigns_1.workflow_state)::text = 'scheduled'::text) THEN 3 WHEN ((campaigns_1.workflow_state)::text = 'paused'::text) THEN 4 ELSE 5 END)
  • Group Key: campaigns.id, (CASE WHEN ((max(ad_groups.id)) IS NULL) THEN 'incomplete_ad_group'::character varying WHEN ((max(ad_group_bid_skus.id)) IS NULL) THEN 'incomplete_ad_group_products'::character varying ELSE campaigns_1.workflow_state END), (CASE WHEN ((max(ad_group_bid_skus.id)) IS NULL) THEN 0 WHEN ((max(ad_groups.id)) IS NULL) THEN 0 WHEN ((campaigns_1.workflow_state)::text = 'ended'::text) THEN 1 WHEN ((campaigns_1.workflow_state)::text = 'out_of_budget'::text) THEN 2 WHEN ((campaigns_1.workflow_state)::text = 'scheduled'::text) THEN 3 WHEN ((campaigns_1.workflow_state)::text = 'paused'::text) THEN 4 ELSE 5 END)
2. 360.600 360.600 ↑ 18,036,575.8 419 1

Sort (cost=1,594,212,165.85..1,613,105,478.96 rows=7,557,325,245 width=40) (actual time=360.580..360.600 rows=419 loops=1)

3. 0.138 360.600 ↑ 18,036,575.8 419 1

Sort (cost=1,594,212,165.85..1,613,105,478.96 rows=7,557,325,245 width=40) (actual time=360.580..360.600 rows=419 loops=1)

  • Sort Key: campaigns.id, (CASE WHEN ((max(ad_groups.id)) IS NULL) THEN 'incomplete_ad_group'::character varying WHEN ((max(ad_group_bid_skus.id)) IS NULL) THEN 'incomplete_ad_group_products'::character varying ELSE campaigns_1.workflow_state END), (CASE WHEN ((max(ad_group_bid_skus.id)) IS NULL) THEN 0 WHEN ((max(ad_groups.id)) IS NULL) THEN 0 WHEN ((campaigns_1.workflow_state)::text = 'ended'::text) THEN 1 WHEN ((campaigns_1.workflow_state)::text = 'out_of_budget'::text) THEN 2 WHEN ((campaigns_1.workflow_state)::text = 'scheduled'::text) THEN 3 WHEN ((campaigns_1.workflow_state)::text = 'paused'::text) THEN 4 ELSE 5 END)
  • Sort Method: quicksort Memory: 44kB
4. 74.981 360.462 ↑ 18,036,575.8 419 1

Hash Left Join (cost=21,582,506.88..112,164,813.08 rows=7,557,325,245 width=40) (actual time=294.577..360.462 rows=419 loops=1)

  • Hash Cond: (campaigns.id = campaigns_1.id)
5. 0.041 2.478 ↑ 266.5 419 1

Nested Loop Left Join (cost=9.56..11,370.42 rows=111,674 width=4) (actual time=0.078..2.478 rows=419 loops=1)

6. 2.264 2.269 ↑ 361.9 7 1

Seq Scan on campaigns (cost=9.13..597.04 rows=2,533 width=4) (actual time=0.063..2.269 rows=7 loops=1)

  • Filter: ((deleted_at IS NULL) AND ((campaign_type)::text = 'featured_product'::text) AND ((account_id = 607) OR (hashed SubPlan 1)) AND ((starts_at >= '2018-12-24 00:00:00'::timestamp without time zone) OR (LEAST(disabled_at, ends_at, (CURRENT_DATE)::timestamp without time zone) >= '2019-01-01 00:00:00'::timestamp without time zone)))
  • Rows Removed by Filter: 10,483
7.          

SubPlan (for Seq Scan)

8. 0.005 0.005 ↓ 0.0 0 1

Index Only Scan using index_account_couplings_on_account_id_and_coupled_account_id on account_couplings (cost=0.15..9.11 rows=9 width=4) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: (account_id = 607)
  • Heap Fetches: 0
9. 0.168 0.168 ↑ 1.5 60 7

Index Only Scan using index_campaign_aggregates_on_campaign_id on campaign_aggregates (cost=0.42..3.37 rows=88 width=4) (actual time=0.006..0.024 rows=60 loops=7)

  • Index Cond: (campaign_id = campaigns.id)
  • Heap Fetches: 237
10. 24.720 283.003 ↑ 67,718.4 10,490 1

Hash (cost=7,153,187.34..7,153,187.34 rows=710,365,999 width=40) (actual time=283.003..283.003 rows=10,490 loops=1)

  • Buckets: 1,048,576 Batches: 2,048 Memory Usage: 8,193kB
11. 3.224 258.283 ↑ 67,718.4 10,490 1

Hash Left Join (cost=44,073.91..7,153,187.34 rows=710,365,999 width=40) (actual time=247.502..258.283 rows=10,490 loops=1)

  • Hash Cond: ((max(ad_group_bids.id)) = ad_group_bid_skus.ad_group_bid_id)
12. 2.901 62.782 ↑ 178.5 10,490 1

Hash Left Join (cost=6,639.05..7,072.72 rows=1,872,612 width=19) (actual time=54.710..62.782 rows=10,490 loops=1)

  • Hash Cond: ((max(ad_groups.id)) = ad_group_bids.ad_group_id)
13. 4.269 17.902 ↑ 1.0 10,490 1

Hash Left Join (cost=1,553.11..1,958.65 rows=10,497 width=15) (actual time=12.430..17.902 rows=10,490 loops=1)

  • Hash Cond: (campaigns_1.id = ad_groups.campaign_id)
14. 1.222 1.222 ↑ 1.0 10,490 1

Seq Scan on campaigns campaigns_1 (cost=0.00..377.97 rows=10,497 width=11) (actual time=0.004..1.222 rows=10,490 loops=1)

15. 0.613 12.411 ↓ 1.1 5,850 1

Hash (cost=1,484.94..1,484.94 rows=5,454 width=8) (actual time=12.411..12.411 rows=5,850 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 293kB
16. 6.832 11.798 ↓ 1.1 5,851 1

HashAggregate (cost=1,375.86..1,430.40 rows=5,454 width=8) (actual time=10.897..11.798 rows=5,851 loops=1)

  • Group Key: ad_groups.campaign_id
17. 4.966 4.966 ↓ 1.0 35,764 1

Seq Scan on ad_groups (cost=0.00..1,201.70 rows=34,832 width=8) (actual time=0.005..4.966 rows=35,764 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 438
18. 4.834 41.979 ↓ 1.0 35,744 1

Hash (cost=4,639.95..4,639.95 rows=35,679 width=8) (actual time=41.979..41.979 rows=35,744 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 1,909kB
19. 21.428 37.145 ↓ 1.0 35,744 1

HashAggregate (cost=3,926.37..4,283.16 rows=35,679 width=8) (actual time=32.170..37.145 rows=35,744 loops=1)

  • Group Key: ad_group_bids.ad_group_id
20. 15.717 15.717 ↓ 1.0 95,111 1

Seq Scan on ad_group_bids (cost=0.00..3,453.06 rows=94,662 width=8) (actual time=0.005..15.717 rows=95,111 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 743
21. 13.578 192.277 ↓ 1.3 95,013 1

Hash (cost=36,486.50..36,486.50 rows=75,869 width=8) (actual time=192.277..192.277 rows=95,013 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,736kB
22. 86.424 178.699 ↓ 1.3 95,013 1

HashAggregate (cost=34,969.12..35,727.81 rows=75,869 width=8) (actual time=164.318..178.699 rows=95,013 loops=1)

  • Group Key: ad_group_bid_skus.ad_group_bid_id
23. 92.275 92.275 ↓ 1.0 444,636 1

Seq Scan on ad_group_bid_skus (cost=0.00..32,776.08 rows=438,607 width=8) (actual time=0.007..92.275 rows=444,636 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 7,208