explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xase

Settings
# exclusive inclusive rows x rows loops node
1. 1,190,250.735 1,962,023.835 ↓ 0.0 0 1

Nested Loop Left Join (cost=7.56..16,826,461.63 rows=1 width=8) (actual time=1,962,023.835..1,962,023.835 rows=0 loops=1)

  • Join Filter: (tr.id = tr_settings.tracker_id)
  • Rows Removed by Join Filter: 18281944578
  • Filter: ((tr_settings.partner_id = 124) OR (tracker_settings.partner_id = 124))
  • Rows Removed by Filter: 64598
2. 53.253 75,342.062 ↓ 12,919.6 64,598 1

Nested Loop Left Join (cost=7.56..16,785,275.96 rows=5 width=20) (actual time=496.461..75,342.062 rows=64,598 loops=1)

3. 8,148.180 44,492.274 ↓ 90.2 451 1

Nested Loop Left Join (cost=0.68..16,784,736.53 rows=5 width=24) (actual time=412.578..44,492.274 rows=451 loops=1)

  • Join Filter: (trackers.id = tracker_settings.tracker_id)
  • Rows Removed by Join Filter: 127637545
4. 0.372 31,476.902 ↓ 90.2 451 1

Append (cost=0.68..16,750,799.01 rows=5 width=20) (actual time=361.228..31,476.902 rows=451 loops=1)

5. 0.000 220.040 ↓ 0.0 0 1

Index Scan using index_trackers_on_name_and_mobile_app_id on trackers (cost=0.68..1,952,006.10 rows=1 width=20) (actual time=220.040..220.040 rows=0 loops=1)

  • Index Cond: (mobile_app_id = 11923)
  • Filter: ((nlevel(name) = 1) AND ((SubPlan 1) = 0))
6.          

SubPlan (for Index Scan)

7. 1.812 31,247.940 ↑ 1.0 1 453

Aggregate (cost=43,269.44..43,269.45 rows=1 width=8) (actual time=68.980..68.980 rows=1 loops=453)

8. 2.290 31,246.128 ↓ 0.0 0 453

Nested Loop (cost=1,190.28..43,269.44 rows=1 width=262) (actual time=68.818..68.976 rows=0 loops=453)

9. 41.223 31,114.758 ↓ 28.4 142 453

Append (cost=1,189.86..43,247.17 rows=5 width=270) (actual time=52.630..68.686 rows=142 loops=453)

10. 9.513 14.496 ↓ 0.0 0 453

Bitmap Heap Scan on trackers trr (cost=1,189.86..14,741.62 rows=1 width=1,190) (actual time=0.032..0.032 rows=0 loops=453)

  • Recheck Cond: (mobile_app_id = trackers.mobile_app_id)
  • Filter: ((network_id = trackers.id) AND (nlevel(name) = 2))
  • Heap Blocks: exact=7836
11. 4.983 4.983 ↑ 499.5 18 453

Bitmap Index Scan on index_trackers_on_mobile_app_id (cost=0.00..1,189.86 rows=8,991 width=0) (actual time=0.011..0.011 rows=18 loops=453)

  • Index Cond: (mobile_app_id = trackers.mobile_app_id)
12. 0.000 0.000 ↓ 0.0 0 453

Seq Scan on adjust_trackers trr_1 (cost=0.00..0.00 rows=1 width=40) (actual time=0.000..0.000 rows=0 loops=453)

  • Filter: ((network_id = trackers.id) AND (mobile_app_id = trackers.mobile_app_id) AND (nlevel(name) = 2))
13. 0.000 0.000 ↓ 0.0 0 453

Seq Scan on facebook_trackers trr_2 (cost=0.00..0.00 rows=1 width=40) (actual time=0.000..0.000 rows=0 loops=453)

  • Filter: ((network_id = trackers.id) AND (mobile_app_id = trackers.mobile_app_id) AND (nlevel(name) = 2))
14. 28,472.409 28,472.409 ↓ 134.0 134 453

Index Scan using adjust_campaign_trackers_mobile_app_id_ltree2text_idx on adjust_campaign_trackers trr_3 (cost=0.69..26,187.15 rows=1 width=40) (actual time=51.410..62.853 rows=134 loops=453)

  • Index Cond: (mobile_app_id = trackers.mobile_app_id)
  • Filter: ((network_id = trackers.id) AND (nlevel(name) = 2))
  • Rows Removed by Filter: 60586
15. 2,178.930 2,586.630 ↓ 8.0 8 453

Bitmap Heap Scan on facebook_campaign_trackers trr_4 (cost=57.32..2,318.40 rows=1 width=40) (actual time=5.677..5.710 rows=8 loops=453)

  • Recheck Cond: (mobile_app_id = trackers.mobile_app_id)
  • Filter: ((network_id = trackers.id) AND (nlevel(name) = 2))
  • Rows Removed by Filter: 3812
  • Heap Blocks: exact=1616304
16. 407.700 407.700 ↓ 3.3 3,820 453

Bitmap Index Scan on facebook_campaign_trackers_mobile_app_id_ltree2text_idx (cost=0.00..57.32 rows=1,168 width=0) (actual time=0.900..0.900 rows=3,820 loops=453)

  • Index Cond: (mobile_app_id = trackers.mobile_app_id)
17. 129.080 129.080 ↓ 0.0 0 64,540

Index Scan using index_tracker_settings_on_tracker_id on tracker_settings trr_settings (cost=0.42..4.44 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=64,540)

  • Index Cond: (tracker_id = trr.id)
  • Filter: (partner_id <> 124)
18. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on adjust_trackers (cost=0.00..0.00 rows=1 width=20) (actual time=0.005..0.005 rows=0 loops=1)

  • Filter: ((deleted_at IS NULL) AND (mobile_app_id = 11923) AND (nlevel(name) = 1) AND ((SubPlan 1) = 0))
19. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on facebook_trackers (cost=0.00..0.00 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: ((deleted_at IS NULL) AND (mobile_app_id = 11923) AND (nlevel(name) = 1) AND ((SubPlan 1) = 0))
20. 31,044.381 31,044.381 ↓ 448.0 448 1

Index Scan using adjust_network_trackers_mobile_app_id_nw_idx on adjust_network_trackers (cost=0.42..14,668,976.49 rows=1 width=20) (actual time=141.181..31,044.381 rows=448 loops=1)

  • Index Cond: (mobile_app_id = 11923)
  • Filter: ((nlevel(name) = 1) AND ((SubPlan 1) = 0))
  • Rows Removed by Filter: 2
21. 212.103 212.103 ↓ 3.0 3 1

Index Scan using facebook_network_trackers_mobile_app_id_nw_idx on facebook_network_trackers (cost=0.29..129,816.42 rows=1 width=20) (actual time=77.212..212.103 rows=3 loops=1)

  • Index Cond: (mobile_app_id = 11923)
  • Filter: ((nlevel(name) = 1) AND ((SubPlan 1) = 0))
22. 4,805.399 4,867.192 ↑ 1.0 283,011 451

Materialize (cost=0.00..12,917.89 rows=289,926 width=12) (actual time=0.001..10.792 rows=283,011 loops=451)

23. 61.793 61.793 ↑ 1.0 283,011 1

Seq Scan on tracker_settings (cost=0.00..11,468.26 rows=289,926 width=12) (actual time=0.011..61.793 rows=283,011 loops=1)

24. 39.688 30,796.535 ↓ 28.6 143 451

Append (cost=6.88..107.83 rows=5 width=20) (actual time=52.137..68.285 rows=143 loops=451)

25. 9.922 15.785 ↓ 0.0 0 451

Bitmap Heap Scan on trackers tr (cost=6.88..96.60 rows=1 width=20) (actual time=0.035..0.035 rows=0 loops=451)

  • Recheck Cond: ((mobile_app_id = trackers.mobile_app_id) AND (mobile_app_id = 11923))
  • Filter: ((network_id = trackers.network_id) AND (nlevel(name) = 2))
  • Heap Blocks: exact=7800
26. 5.863 5.863 ↑ 2.5 18 451

Bitmap Index Scan on index_trackers_on_mobile_app_id (cost=0.00..6.88 rows=45 width=0) (actual time=0.013..0.013 rows=18 loops=451)

  • Index Cond: ((mobile_app_id = trackers.mobile_app_id) AND (mobile_app_id = 11923))
27. 0.000 0.000 ↓ 0.0 0 451

Seq Scan on adjust_trackers tr_1 (cost=0.00..0.00 rows=1 width=20) (actual time=0.000..0.000 rows=0 loops=451)

  • Filter: ((mobile_app_id = 11923) AND (mobile_app_id = trackers.mobile_app_id) AND (network_id = trackers.network_id) AND (nlevel(name) = 2))
28. 0.000 0.000 ↓ 0.0 0 451

Seq Scan on facebook_trackers tr_2 (cost=0.00..0.00 rows=1 width=20) (actual time=0.000..0.000 rows=0 loops=451)

  • Filter: ((mobile_app_id = 11923) AND (mobile_app_id = trackers.mobile_app_id) AND (network_id = trackers.network_id) AND (nlevel(name) = 2))
29. 28,891.511 28,891.511 ↓ 135.0 135 451

Index Scan using idx_adjust_campaign_trackers_on_app_and_name on adjust_campaign_trackers tr_3 (cost=0.69..6.65 rows=1 width=20) (actual time=51.269..64.061 rows=135 loops=451)

  • Index Cond: ((mobile_app_id = trackers.mobile_app_id) AND (mobile_app_id = 11923))
  • Filter: ((network_id = trackers.network_id) AND (nlevel(name) = 2))
  • Rows Removed by Filter: 60585
30. 1,849.551 1,849.551 ↓ 8.0 8 451

Index Scan using facebook_campaign_trackers_mobile_app_id_ltree2text_idx on facebook_campaign_trackers tr_4 (cost=0.56..4.58 rows=1 width=20) (actual time=4.029..4.101 rows=8 loops=451)

  • Index Cond: ((mobile_app_id = trackers.mobile_app_id) AND (mobile_app_id = 11923))
  • Filter: ((network_id = trackers.network_id) AND (nlevel(name) = 2))
  • Rows Removed by Filter: 3812
31. 696,364.051 696,431.038 ↑ 1.0 283,011 64,598

Materialize (cost=0.00..12,917.89 rows=289,926 width=12) (actual time=0.001..10.781 rows=283,011 loops=64,598)

32. 66.987 66.987 ↑ 1.0 283,011 1

Seq Scan on tracker_settings tr_settings (cost=0.00..11,468.26 rows=289,926 width=12) (actual time=0.005..66.987 rows=283,011 loops=1)

Planning time : 38.202 ms
Execution time : 1,962,031.477 ms