explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IBw5

Settings
# exclusive inclusive rows x rows loops node
1. 1.257 135.535 ↑ 12,483,308.9 2,787 1

Merge Join (cost=12,907,738.93..534,795,358.76 rows=34,790,981,965 width=3,818) (actual time=133.511..135.535 rows=2,787 loops=1)

  • Merge Cond: (a.id = ad.account_id)
2.          

CTE a

3. 0.155 1.524 ↑ 1,736.8 554 1

Merge Left Join (cost=162.96..324.52 rows=962,165 width=1,223) (actual time=0.114..1.524 rows=554 loops=1)

  • Merge Cond: (u.id = m.user_id)
4. 0.175 1.310 ↑ 1.7 554 1

Merge Join (cost=1.53..16.35 rows=967 width=1,009) (actual time=0.060..1.310 rows=554 loops=1)

  • Merge Cond: (a_1.buyer_id = u.id)
5. 0.112 1.088 ↑ 1.7 555 1

Nested Loop Left Join (cost=0.42..475.10 rows=967 width=1,004) (actual time=0.013..1.088 rows=555 loops=1)

6. 0.421 0.421 ↑ 1.7 555 1

Index Scan using facebook__accounts_buyer_id_0ebffa3e on facebook__accounts a_1 (cost=0.28..265.12 rows=967 width=52) (actual time=0.008..0.421 rows=555 loops=1)

7. 0.555 0.555 ↓ 0.0 0 555

Index Scan using content__sites_account_id_d97fd1e2 on content__sites s (cost=0.14..0.21 rows=1 width=956) (actual time=0.001..0.001 rows=0 loops=555)

  • Index Cond: (account_id = a_1.id)
8. 0.025 0.047 ↓ 14.0 28 1

Sort (cost=1.11..1.12 rows=2 width=9) (actual time=0.044..0.047 rows=28 loops=1)

  • Sort Key: u.id
  • Sort Method: quicksort Memory: 26kB
9. 0.022 0.022 ↓ 16.5 33 1

Seq Scan on core_user u (cost=0.00..1.10 rows=2 width=9) (actual time=0.011..0.022 rows=33 loops=1)

  • Filter: (id = ANY ('{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33}'::integer[]))
10. 0.021 0.059 ↑ 25.8 77 1

Sort (cost=161.43..166.40 rows=1,990 width=222) (actual time=0.050..0.059 rows=77 loops=1)

  • Sort Key: m.user_id
  • Sort Method: quicksort Memory: 25kB
11. 0.020 0.038 ↑ 331.7 6 1

Hash Left Join (cost=17.20..52.39 rows=1,990 width=222) (actual time=0.035..0.038 rows=6 loops=1)

  • Hash Cond: (m.team_id = t.id)
12. 0.005 0.005 ↑ 331.7 6 1

Seq Scan on adminapp_member m (cost=0.00..29.90 rows=1,990 width=8) (actual time=0.004..0.005 rows=6 loops=1)

13. 0.008 0.013 ↑ 64.0 5 1

Hash (cost=13.20..13.20 rows=320 width=222) (actual time=0.013..0.013 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.005 0.005 ↑ 64.0 5 1

Seq Scan on adminapp_team t (cost=0.00..13.20 rows=320 width=222) (actual time=0.004..0.005 rows=5 loops=1)

15.          

CTE ad

16. 0.968 3.243 ↑ 2,105.9 3,434 1

Hash Join (cost=297.03..124,331.52 rows=7,231,812 width=441) (actual time=2.229..3.243 rows=3,434 loops=1)

  • Hash Cond: (a_2.id = ad_4.account_id)
17. 0.085 0.085 ↑ 1,736.8 554 1

CTE Scan on a a_2 (cost=0.00..19,243.30 rows=962,165 width=4) (actual time=0.000..0.085 rows=554 loops=1)

18. 1.041 2.190 ↑ 1.0 4,179 1

Hash (cost=244.79..244.79 rows=4,179 width=441) (actual time=2.190..2.190 rows=4,179 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 293kB
19. 1.149 1.149 ↑ 1.0 4,179 1

Seq Scan on facebook__ad_accounts ad_4 (cost=0.00..244.79 rows=4,179 width=441) (actual time=0.009..1.149 rows=4,179 loops=1)

20. 0.407 2.503 ↑ 1,736.8 554 1

Sort (cost=1,390,861.40..1,393,266.81 rows=962,165 width=3,074) (actual time=2.457..2.503 rows=554 loops=1)

  • Sort Key: a.id
  • Sort Method: quicksort Memory: 68kB
21. 2.096 2.096 ↑ 1,736.8 554 1

CTE Scan on a (cost=0.00..19,243.30 rows=962,165 width=3,074) (actual time=0.117..2.096 rows=554 loops=1)

22. 0.514 131.775 ↑ 2,594.8 2,787 1

Materialize (cost=11,392,221.50..11,428,380.56 rows=7,231,812 width=716) (actual time=131.049..131.775 rows=2,787 loops=1)

23. 1.294 131.261 ↑ 2,594.8 2,787 1

Sort (cost=11,392,221.50..11,410,301.03 rows=7,231,812 width=716) (actual time=131.043..131.261 rows=2,787 loops=1)

  • Sort Key: ad.account_id
  • Sort Method: quicksort Memory: 324kB
24. 0.930 129.967 ↑ 2,594.8 2,787 1

Hash Join (cost=8,066,724.28..8,269,517.84 rows=7,231,812 width=716) (actual time=126.767..129.967 rows=2,787 loops=1)

  • Hash Cond: (conv.adaccount_id = camp.adaccount_id)
25. 0.930 117.825 ↑ 2,594.8 2,787 1

Hash Join (cost=7,591,288.05..7,774,695.84 rows=7,231,812 width=720) (actual time=115.536..117.825 rows=2,787 loops=1)

  • Hash Cond: (conv.adaccount_id = cost.adaccount_id)
26. 0.953 93.841 ↑ 2,594.8 2,787 1

Hash Join (cost=5,562,730.71..5,726,752.72 rows=7,231,812 width=684) (actual time=92.460..93.841 rows=2,787 loops=1)

  • Hash Cond: (ad.id = conv.adaccount_id)
27. 0.450 0.450 ↑ 2,105.9 3,434 1

CTE Scan on ad (cost=0.00..144,636.24 rows=7,231,812 width=648) (actual time=0.001..0.450 rows=3,434 loops=1)

28. 0.964 92.438 ↓ 13.9 2,787 1

Hash (cost=5,562,728.21..5,562,728.21 rows=200 width=36) (actual time=92.438..92.438 rows=2,787 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 143kB
29. 0.438 91.474 ↓ 13.9 2,787 1

Subquery Scan on conv (cost=968,554.47..5,562,728.21 rows=200 width=36) (actual time=1.385..91.474 rows=2,787 loops=1)

30. 11.880 91.036 ↓ 13.9 2,787 1

GroupAggregate (cost=968,554.47..5,562,726.21 rows=200 width=36) (actual time=1.384..91.036 rows=2,787 loops=1)

  • Group Key: ad_1.id
31. 20.929 79.156 ↑ 2,766.7 82,755 1

Merge Join (cost=968,554.47..4,417,946.81 rows=228,955,380 width=9) (actual time=1.348..79.156 rows=82,755 loops=1)

  • Merge Cond: (c.adaccount_id = ad_1.id)
32. 11.613 50.994 ↑ 1.2 83,251 1

Nested Loop Left Join (cost=0.57..14,805.47 rows=95,992 width=9) (actual time=0.030..50.994 rows=83,251 loops=1)

33. 3.002 3.002 ↑ 1.0 5,197 1

Index Scan using facebook__ad_campaigns_adaccount_id_df68d37e on facebook__ad_campaigns c (cost=0.28..668.89 rows=5,197 width=8) (actual time=0.022..3.002 rows=5,197 loops=1)

34. 36.379 36.379 ↑ 3.7 15 5,197

Index Scan using tracker_kei_campaig_94b676_idx on tracker_keitaroconversion conv_1 (cost=0.29..2.17 rows=55 width=9) (actual time=0.001..0.007 rows=15 loops=5,197)

  • Index Cond: (campaign_id = c.keitaro_campaign_id)
35. 6.772 7.233 ↑ 86.7 83,400 1

Sort (cost=968,553.90..986,633.43 rows=7,231,812 width=4) (actual time=1.315..7.233 rows=83,400 loops=1)

  • Sort Key: ad_1.id
  • Sort Method: quicksort Memory: 257kB
36. 0.461 0.461 ↑ 2,105.9 3,434 1

CTE Scan on ad ad_1 (cost=0.00..144,636.24 rows=7,231,812 width=4) (actual time=0.001..0.461 rows=3,434 loops=1)

37. 0.644 23.054 ↓ 17.2 3,434 1

Hash (cost=2,028,554.84..2,028,554.84 rows=200 width=36) (actual time=23.054..23.054 rows=3,434 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 171kB
38. 0.398 22.410 ↓ 17.2 3,434 1

Subquery Scan on cost (cost=2,028,550.34..2,028,554.84 rows=200 width=36) (actual time=20.898..22.410 rows=3,434 loops=1)

39. 7.842 22.012 ↓ 17.2 3,434 1

HashAggregate (cost=2,028,550.34..2,028,552.84 rows=200 width=36) (actual time=20.897..22.012 rows=3,434 loops=1)

  • Group Key: ad_2.id
40. 4.860 14.170 ↑ 2,942.8 25,860 1

Hash Left Join (cost=1,150.82..1,648,049.49 rows=76,100,170 width=10) (actual time=8.941..14.170 rows=25,860 loops=1)

  • Hash Cond: (ad_2.id = cost_1.adaccount_id)
41. 0.488 0.488 ↑ 2,105.9 3,434 1

CTE Scan on ad ad_2 (cost=0.00..144,636.24 rows=7,231,812 width=4) (actual time=0.000..0.488 rows=3,434 loops=1)

42. 4.733 8.822 ↓ 1.0 25,204 1

Hash (cost=835.92..835.92 rows=25,192 width=10) (actual time=8.822..8.822 rows=25,204 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1338kB
43. 4.089 4.089 ↓ 1.0 25,204 1

Seq Scan on facebook__ad_costs cost_1 (cost=0.00..835.92 rows=25,192 width=10) (actual time=0.017..4.089 rows=25,204 loops=1)

44. 0.634 11.212 ↓ 17.2 3,434 1

Hash (cost=475,433.73..475,433.73 rows=200 width=12) (actual time=11.212..11.212 rows=3,434 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 180kB
45. 0.384 10.578 ↓ 17.2 3,434 1

Subquery Scan on camp (cost=475,429.73..475,433.73 rows=200 width=12) (actual time=9.650..10.578 rows=3,434 loops=1)

46. 2.245 10.194 ↓ 17.2 3,434 1

HashAggregate (cost=475,429.73..475,431.73 rows=200 width=12) (actual time=9.648..10.194 rows=3,434 loops=1)

  • Group Key: ad_3.id
47. 1.417 7.949 ↑ 2,313.5 5,358 1

Hash Left Join (cost=221.93..413,451.29 rows=12,395,688 width=12) (actual time=4.109..7.949 rows=5,358 loops=1)

  • Hash Cond: (ad_3.id = c_1.adaccount_id)
48. 4.699 4.699 ↑ 2,105.9 3,434 1

CTE Scan on ad ad_3 (cost=0.00..144,636.24 rows=7,231,812 width=4) (actual time=2.230..4.699 rows=3,434 loops=1)

49. 0.969 1.833 ↑ 1.0 5,197 1

Hash (cost=156.97..156.97 rows=5,197 width=12) (actual time=1.833..1.833 rows=5,197 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 288kB
50. 0.864 0.864 ↑ 1.0 5,197 1

Seq Scan on facebook__ad_campaigns c_1 (cost=0.00..156.97 rows=5,197 width=12) (actual time=0.008..0.864 rows=5,197 loops=1)

Planning time : 4.367 ms
Execution time : 136.220 ms