explain.depesz.com

PostgreSQL's explain analyze made readable

Result: O6ye

Settings
# exclusive inclusive rows x rows loops node
1. 0.820 5,833.616 ↑ 1.1 1,196 1

Subquery Scan on sub_ag (cost=10,999.73..27,556.59 rows=1,283 width=258) (actual time=88.114..5,833.616 rows=1,196 loops=1)

2. 2,765.131 5,832.796 ↑ 1.1 1,196 1

Nested Loop Left Join (cost=10,999.73..27,543.76 rows=1,283 width=191) (actual time=88.114..5,832.796 rows=1,196 loops=1)

3. 1.284 168.561 ↑ 1.1 1,196 1

Merge Left Join (cost=10,990.92..15,547.71 rows=1,283 width=170) (actual time=88.063..168.561 rows=1,196 loops=1)

  • Merge Cond: (cmp.id = paa.campaign_id)
4. 2.666 161.665 ↑ 1.1 1,196 1

Merge Left Join (cost=10,845.11..15,348.88 rows=1,283 width=170) (actual time=86.055..161.665 rows=1,196 loops=1)

  • Merge Cond: (cmp.id = cc_2.campaign_id)
5. 1.765 69.674 ↑ 1.1 1,196 1

Nested Loop Left Join (cost=6,994.74..10,665.47 rows=1,283 width=170) (actual time=35.936..69.674 rows=1,196 loops=1)

6. 0.974 65.517 ↑ 1.1 1,196 1

Nested Loop Left Join (cost=6,994.45..8,570.98 rows=1,283 width=165) (actual time=35.930..65.517 rows=1,196 loops=1)

7. 3.111 60.955 ↑ 1.1 1,196 1

Merge Left Join (cost=6,994.17..7,605.91 rows=1,283 width=160) (actual time=35.921..60.955 rows=1,196 loops=1)

  • Merge Cond: (cmp.id = cc_1.campaign_id)
8. 1.271 17.564 ↑ 1.1 1,196 1

Sort (cost=6,020.86..6,024.06 rows=1,283 width=160) (actual time=17.112..17.564 rows=1,196 loops=1)

  • Sort Key: cmp.id
  • Sort Method: quicksort Memory: 381kB
9. 0.667 16.293 ↑ 1.1 1,196 1

Hash Left Join (cost=4,614.42..5,954.62 rows=1,283 width=160) (actual time=10.246..16.293 rows=1,196 loops=1)

  • Hash Cond: (cmp.payout_model_id = cca.id)
10. 0.737 15.610 ↑ 1.1 1,196 1

Hash Left Join (cost=4,612.97..5,935.53 rows=1,283 width=151) (actual time=10.224..15.610 rows=1,196 loops=1)

  • Hash Cond: (ad.manager_id = aum.id)
11. 0.792 6.815 ↑ 1.1 1,196 1

Hash Left Join (cost=255.86..1,556.35 rows=1,283 width=135) (actual time=2.157..6.815 rows=1,196 loops=1)

  • Hash Cond: (cmp.advertiser_id = ad.id)
12. 3.885 3.885 ↑ 1.1 1,196 1

Seq Scan on campaigns cmp (cost=0.00..1,279.64 rows=1,283 width=108) (actual time=0.011..3.885 rows=1,196 loops=1)

  • Filter: (((NOT setup_mode) OR (setup_mode IS NULL)) AND ((status)::text = 'active'::text))
  • Rows Removed by Filter: 4434
13. 1.030 2.138 ↑ 1.0 3,016 1

Hash (cost=218.16..218.16 rows=3,016 width=35) (actual time=2.138..2.138 rows=3,016 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 207kB
14. 1.108 1.108 ↑ 1.0 3,016 1

Seq Scan on advertisers ad (cost=0.00..218.16 rows=3,016 width=35) (actual time=0.002..1.108 rows=3,016 loops=1)

15. 2.521 8.058 ↑ 1.2 8,596 1

Hash (cost=4,225.94..4,225.94 rows=10,494 width=16) (actual time=8.058..8.058 rows=8,596 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 392kB
16. 5.537 5.537 ↑ 1.2 8,596 1

Seq Scan on users aum (cost=0.00..4,225.94 rows=10,494 width=16) (actual time=0.004..5.537 rows=8,596 loops=1)

17. 0.007 0.016 ↑ 1.0 20 1

Hash (cost=1.20..1.20 rows=20 width=21) (actual time=0.016..0.016 rows=20 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
18. 0.009 0.009 ↑ 1.0 20 1

Seq Scan on campaign_conversion_aliases cca (cost=0.00..1.20 rows=20 width=21) (actual time=0.002..0.009 rows=20 loops=1)

19. 3.300 40.280 ↓ 1.0 5,628 1

Materialize (cost=973.31..1,552.48 rows=5,626 width=8) (actual time=18.725..40.280 rows=5,628 loops=1)

20. 16.311 36.980 ↓ 1.0 5,628 1

GroupAggregate (cost=973.31..1,482.16 rows=5,626 width=29) (actual time=18.723..36.980 rows=5,628 loops=1)

  • Group Key: cc_1.campaign_id
21. 3.970 20.669 ↓ 1.0 5,797 1

Sort (cost=973.31..987.79 rows=5,793 width=29) (actual time=18.705..20.669 rows=5,797 loops=1)

  • Sort Key: cc_1.campaign_id
  • Sort Method: quicksort Memory: 641kB
22. 12.100 16.699 ↓ 1.0 5,798 1

Sort (cost=538.83..553.32 rows=5,793 width=29) (actual time=15.541..16.699 rows=5,798 loops=1)

  • Sort Key: c.name
  • Sort Method: quicksort Memory: 641kB
23. 3.033 4.599 ↓ 1.0 5,798 1

Hash Left Join (cost=6.19..176.77 rows=5,793 width=29) (actual time=0.130..4.599 rows=5,798 loops=1)

  • Hash Cond: (cc_1.category_id = c.id)
24. 1.449 1.449 ↓ 1.0 5,798 1

Seq Scan on campaign_categories cc_1 (cost=0.00..90.93 rows=5,793 width=12) (actual time=0.008..1.449 rows=5,798 loops=1)

25. 0.061 0.117 ↑ 1.0 186 1

Hash (cost=3.86..3.86 rows=186 width=25) (actual time=0.117..0.117 rows=186 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
26. 0.056 0.056 ↑ 1.0 186 1

Seq Scan on categories c (cost=0.00..3.86 rows=186 width=25) (actual time=0.002..0.056 rows=186 loops=1)

27. 3.588 3.588 ↑ 1.0 1 1,196

Index Scan using advertiser_contact_pkey on contacts acm (cost=0.29..0.74 rows=1 width=21) (actual time=0.002..0.003 rows=1 loops=1,196)

  • Index Cond: (id = aum.primary_contact_id)
28. 2.392 2.392 ↑ 1.0 1 1,196

Index Scan using user_profiles_user_id_uniq on user_profiles mup (cost=0.29..1.62 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=1,196)

  • Index Cond: (user_id = aum.id)
29. 3.589 89.325 ↓ 1.0 5,628 1

Materialize (cost=3,850.37..4,654.05 rows=5,625 width=8) (actual time=50.013..89.325 rows=5,628 loops=1)

30. 29.473 85.736 ↓ 1.0 5,628 1

GroupAggregate (cost=3,850.37..4,583.74 rows=5,625 width=23) (actual time=50.011..85.736 rows=5,628 loops=1)

  • Group Key: cc_2.campaign_id
31. 13.208 56.263 ↓ 1.0 20,772 1

Sort (cost=3,850.37..3,902.29 rows=20,766 width=23) (actual time=50.000..56.263 rows=20,772 loops=1)

  • Sort Key: cc_2.campaign_id
  • Sort Method: quicksort Memory: 2391kB
32. 27.071 43.055 ↓ 1.0 20,773 1

Sort (cost=2,101.68..2,153.59 rows=20,766 width=23) (actual time=38.955..43.055 rows=20,773 loops=1)

  • Sort Key: c_1.name
  • Sort Method: quicksort Memory: 2391kB
33. 11.032 15.984 ↓ 1.0 20,773 1

Hash Left Join (cost=1.36..612.55 rows=20,766 width=23) (actual time=0.024..15.984 rows=20,773 loops=1)

  • Hash Cond: (cc_2.channel_id = c_1.id)
34. 4.940 4.940 ↓ 1.0 20,773 1

Seq Scan on campaign_channels cc_2 (cost=0.00..325.66 rows=20,766 width=12) (actual time=0.006..4.940 rows=20,773 loops=1)

35. 0.007 0.012 ↑ 1.0 16 1

Hash (cost=1.16..1.16 rows=16 width=19) (actual time=0.012..0.012 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
36. 0.005 0.005 ↑ 1.0 16 1

Seq Scan on channels c_1 (cost=0.00..1.16 rows=16 width=19) (actual time=0.001..0.005 rows=16 loops=1)

37. 0.583 5.612 ↓ 1.0 973 1

Materialize (cost=145.81..191.11 rows=971 width=8) (actual time=2.007..5.612 rows=973 loops=1)

38. 2.473 5.029 ↓ 1.0 973 1

GroupAggregate (cost=145.81..178.97 rows=971 width=24) (actual time=2.005..5.029 rows=973 loops=1)

  • Group Key: paa.campaign_id
39. 1.050 2.556 ↓ 1.0 1,619 1

Sort (cost=145.81..149.85 rows=1,617 width=24) (actual time=1.999..2.556 rows=1,619 loops=1)

  • Sort Key: paa.campaign_id
  • Sort Method: quicksort Memory: 175kB
40. 1.078 1.506 ↓ 1.0 1,619 1

Hash Left Join (cost=10.22..59.63 rows=1,617 width=24) (actual time=0.188..1.506 rows=1,619 loops=1)

  • Hash Cond: (paa.attribute_id = cjca.id)
41. 0.252 0.252 ↓ 1.0 1,619 1

Seq Scan on campaign_attribute_assoc paa (cost=0.00..27.17 rows=1,617 width=16) (actual time=0.007..0.252 rows=1,619 loops=1)

42. 0.089 0.176 ↑ 1.0 321 1

Hash (cost=6.21..6.21 rows=321 width=8) (actual time=0.176..0.176 rows=321 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
43. 0.087 0.087 ↑ 1.0 321 1

Seq Scan on campaign_attribute cjca (cost=0.00..6.21 rows=321 width=8) (actual time=0.002..0.087 rows=321 loops=1)

44. 1.196 2,899.104 ↓ 0.0 0 1,196

Subquery Scan on cc (cost=8.81..8.83 rows=1 width=29) (actual time=2.424..2.424 rows=0 loops=1,196)

  • Filter: (cc.campaign_id = cmp.id)
45. 1.196 2,897.908 ↓ 0.0 0 1,196

Limit (cost=8.81..8.82 rows=1 width=29) (actual time=2.423..2.423 rows=0 loops=1,196)

46. 2.392 2,896.712 ↓ 0.0 0 1,196

Sort (cost=8.81..8.82 rows=1 width=29) (actual time=2.422..2.422 rows=0 loops=1,196)

  • Sort Key: (CASE campaign_caps.unit WHEN 'monetary'::campaign_cap_unit THEN (campaign_caps.monetary_max - campaign_cap_monetary_used_get(cmp.id, campaign_caps.period, campaign_caps.unit, NULL::bigint)) ELSE ((campaign_caps.conversi (...)
  • Sort Method: quicksort Memory: 25kB
47. 2,894.320 2,894.320 ↓ 0.0 0 1,196

Index Scan using campaign_caps_campaign_id_idx on campaign_caps (cost=0.28..8.80 rows=1 width=29) (actual time=2.399..2.420 rows=0 loops=1,196)

  • Index Cond: (campaign_id = cmp.id)