explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ibnl

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

Merge Join (cost=8,690,209.27..530,577,829.10 rows=34,790,981,965 width=3,814) (actual time=134.057..137.475 rows=2,787 loops=1)

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

CTE a

3. 0.285 2.684 ↑ 1,736.8 554 1

Merge Left Join (cost=162.96..325.17 rows=962,165 width=1,234) (actual time=0.150..2.684 rows=554 loops=1)

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

Merge Join (cost=1.53..16.99 rows=967 width=1,020) (actual time=0.094..2.330 rows=554 loops=1)

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

Nested Loop Left Join (cost=0.42..496.45 rows=967 width=1,015) (actual time=0.028..1.944 rows=555 loops=1)

6. 0.733 0.733 ↑ 1.7 555 1

Index Scan using facebook__accounts_buyer_id_0ebffa3e on facebook__accounts a_1 (cost=0.28..286.47 rows=967 width=63) (actual time=0.018..0.733 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.026 0.064 ↓ 14.0 28 1

Sort (cost=1.12..1.12 rows=2 width=9) (actual time=0.060..0.064 rows=28 loops=1)

  • Sort Key: u.id
  • Sort Method: quicksort Memory: 26kB
9. 0.038 0.038 ↓ 17.0 34 1

Seq Scan on core_user u (cost=0.00..1.11 rows=2 width=9) (actual time=0.017..0.038 rows=34 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,34,45}'::integer[]))
10. 0.022 0.069 ↑ 25.8 77 1

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

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

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

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

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

13. 0.006 0.015 ↑ 64.0 5 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
14. 0.009 0.009 ↑ 64.0 5 1

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

15.          

CTE ad

16. 1.585 5.553 ↑ 2,105.9 3,434 1

Hash Join (cost=297.03..124,331.52 rows=7,231,812 width=34) (actual time=3.832..5.553 rows=3,434 loops=1)

  • Hash Cond: (a_2.id = ad_4.account_id)
17. 0.150 0.150 ↑ 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.001..0.150 rows=554 loops=1)

18. 1.801 3.818 ↑ 1.0 4,179 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 342kB
19. 2.017 2.017 ↑ 1.0 4,179 1

Seq Scan on facebook__ad_accounts ad_4 (cost=0.00..244.79 rows=4,179 width=34) (actual time=0.014..2.017 rows=4,179 loops=1)

20. 0.701 4.441 ↑ 1,736.8 554 1

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

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

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

22. 0.931 130.980 ↑ 2,594.8 2,787 1

Materialize (cost=7,174,691.19..7,210,850.25 rows=7,231,812 width=744) (actual time=129.679..130.980 rows=2,787 loops=1)

23. 2.326 130.049 ↑ 2,594.8 2,787 1

Sort (cost=7,174,691.19..7,192,770.72 rows=7,231,812 width=744) (actual time=129.674..130.049 rows=2,787 loops=1)

  • Sort Key: ad.account_id
  • Sort Method: quicksort Memory: 639kB
24. 1.849 127.723 ↑ 2,594.8 2,787 1

Hash Join (cost=3,775,039.47..3,977,833.03 rows=7,231,812 width=744) (actual time=121.656..127.723 rows=2,787 loops=1)

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

Hash Join (cost=3,237,624.30..3,421,032.09 rows=7,231,812 width=720) (actual time=98.863..103.090 rows=2,787 loops=1)

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

Hash Join (cost=1,708,010.22..1,872,032.24 rows=7,231,812 width=684) (actual time=69.856..72.438 rows=2,787 loops=1)

  • Hash Cond: (ad.id = conv.adaccount_id)
27. 0.794 0.794 ↑ 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.794 rows=3,434 loops=1)

28. 0.953 69.833 ↓ 13.9 2,787 1

Hash (cost=1,708,007.72..1,708,007.72 rows=200 width=36) (actual time=69.833..69.833 rows=2,787 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 138kB
29. 0.612 68.880 ↓ 13.9 2,787 1

Subquery Scan on conv (cost=1,708,003.22..1,708,007.72 rows=200 width=36) (actual time=67.049..68.880 rows=2,787 loops=1)

30. 12.888 68.268 ↓ 13.9 2,787 1

HashAggregate (cost=1,708,003.22..1,708,005.72 rows=200 width=36) (actual time=67.048..68.268 rows=2,787 loops=1)

  • Group Key: ad_1.id
31. 10.415 55.380 ↑ 1,975.2 31,032 1

Hash Join (cost=2,104.54..1,401,526.94 rows=61,295,256 width=9) (actual time=44.244..55.380 rows=31,032 loops=1)

  • Hash Cond: (ad_1.id = c.adaccount_id)
32. 0.843 0.843 ↑ 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.000..0.843 rows=3,434 loops=1)

33. 10.524 44.122 ↓ 1.2 31,528 1

Hash (cost=1,783.30..1,783.30 rows=25,699 width=9) (actual time=44.121..44.122 rows=31,528 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,416kB
34. 13.693 33.598 ↓ 1.2 31,528 1

Hash Right Join (cost=222.22..1,783.30 rows=25,699 width=9) (actual time=3.412..33.598 rows=31,528 loops=1)

  • Hash Cond: (conv_1.campaign_id = c.keitaro_campaign_id)
35. 16.603 16.603 ↓ 1.2 29,950 1

Index Scan using tracker_kei_date_6615e4_idx on tracker_keitaroconversion conv_1 (cost=0.29..1,020.32 rows=25,250 width=9) (actual time=0.042..16.603 rows=29,950 loops=1)

  • Index Cond: ((date >= '2019-12-01'::date) AND (date <= '2020-01-11'::date))
36. 1.678 3.302 ↑ 1.0 5,197 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 258kB
37. 1.624 1.624 ↑ 1.0 5,197 1

Seq Scan on facebook__ad_campaigns c (cost=0.00..156.97 rows=5,197 width=8) (actual time=0.026..1.624 rows=5,197 loops=1)

38. 1.126 28.995 ↓ 17.2 3,434 1

Hash (cost=1,529,611.57..1,529,611.57 rows=200 width=36) (actual time=28.995..28.995 rows=3,434 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 164kB
39. 0.766 27.869 ↓ 17.2 3,434 1

Subquery Scan on cost (cost=1,529,607.07..1,529,611.57 rows=200 width=36) (actual time=25.424..27.869 rows=3,434 loops=1)

40. 9.322 27.103 ↓ 17.2 3,434 1

HashAggregate (cost=1,529,607.07..1,529,609.57 rows=200 width=36) (actual time=25.423..27.103 rows=3,434 loops=1)

  • Group Key: ad_2.id
41. 4.929 17.781 ↑ 2,709.2 15,812 1

Hash Left Join (cost=1,139.14..1,315,416.75 rows=42,838,064 width=10) (actual time=12.178..17.781 rows=15,812 loops=1)

  • Hash Cond: (ad_2.id = cost_1.adaccount_id)
42. 0.825 0.825 ↑ 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.001..0.825 rows=3,434 loops=1)

43. 4.507 12.027 ↓ 1.0 14,193 1

Hash (cost=961.88..961.88 rows=14,181 width=10) (actual time=12.027..12.027 rows=14,193 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 738kB
44. 7.520 7.520 ↓ 1.0 14,193 1

Seq Scan on facebook__ad_costs cost_1 (cost=0.00..961.88 rows=14,181 width=10) (actual time=1.945..7.520 rows=14,193 loops=1)

  • Filter: ((date >= '2019-12-01'::date) AND (date <= '2020-01-11'::date))
  • Rows Removed by Filter: 11,011
45. 1.273 22.784 ↓ 17.2 3,434 1

Hash (cost=537,412.67..537,412.67 rows=200 width=36) (actual time=22.784..22.784 rows=3,434 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 349kB
46. 0.766 21.511 ↓ 17.2 3,434 1

Subquery Scan on camp (cost=537,408.17..537,412.67 rows=200 width=36) (actual time=18.469..21.511 rows=3,434 loops=1)

47. 7.048 20.745 ↓ 17.2 3,434 1

HashAggregate (cost=537,408.17..537,410.67 rows=200 width=36) (actual time=18.468..20.745 rows=3,434 loops=1)

  • Group Key: ad_3.id
48. 2.390 13.697 ↑ 2,313.5 5,358 1

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

  • Hash Cond: (ad_3.id = c_1.adaccount_id)
49. 8.115 8.115 ↑ 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=3.833..8.115 rows=3,434 loops=1)

50. 1.631 3.192 ↑ 1.0 5,197 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 288kB
51. 1.561 1.561 ↑ 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.014..1.561 rows=5,197 loops=1)

Planning time : 3.122 ms
Execution time : 138.151 ms