explain.depesz.com

PostgreSQL's explain analyze made readable

Result: isOT

Settings
# exclusive inclusive rows x rows loops node
1. 0.225 300,112.162 ↑ 2,637,700,987.0 2 1

Hash Left Join (cost=2,659,493.71..553,086,736.93 rows=5,275,401,974 width=1,253) (actual time=300,112.094..300,112.162 rows=2 loops=1)

  • Hash Cond: (mg.brand_id = b.brand_id)
2. 0.020 300,111.920 ↑ 2,637,700,987.0 2 1

Hash Left Join (cost=2,659,492.35..222,421,242.71 rows=5,275,401,974 width=1,384) (actual time=300,111.873..300,111.920 rows=2 loops=1)

  • Hash Cond: (c.marketing_group_id = mg.marketing_group_id)
3. 0.018 300,111.825 ↑ 2,637,700,987.0 2 1

Hash Left Join (cost=2,659,474.47..208,521,760.25 rows=5,275,401,974 width=1,376) (actual time=300,111.780..300,111.825 rows=2 loops=1)

  • Hash Cond: (qa.qa_room_id = qr2.qa_room_id)
4. 0.039 300,111.137 ↑ 2,637,700,987.0 2 1

Hash Join (cost=2,659,461.55..194,342,857.19 rows=5,275,401,974 width=1,348) (actual time=300,111.093..300,111.137 rows=2 loops=1)

  • Hash Cond: (c.actor_id = act.actor_id)
5. 0.047 300,105.704 ↑ 2,637,700,987.0 2 1

Hash Left Join (cost=2,659,272.76..180,481,647.80 rows=5,275,401,974 width=1,148) (actual time=300,105.661..300,105.704 rows=2 loops=1)

  • Hash Cond: (s.advertiser_unit_id = adv.advertiser_unit_id)
6. 18,216.062 300,105.529 ↑ 2,637,700,987.0 2 1

Merge Join (cost=2,659,252.18..166,508,827.15 rows=5,275,401,974 width=1,020) (actual time=300,105.489..300,105.529 rows=2 loops=1)

  • Merge Cond: (p.prospect_id = c.prospect_id)
7. 73,594.840 276,914.614 ↑ 1.1 83,784,773 1

Merge Join (cost=375.39..10,021,520.22 rows=94,640,182 width=102) (actual time=0.033..276,914.614 rows=83,784,773 loops=1)

  • Merge Cond: (p.prospect_id = pd.prospect_id)
8. 24,890.103 24,890.103 ↑ 1.1 83,767,530 1

Index Only Scan using pk_prospects on prospects p (cost=0.57..2,456,015.20 rows=94,548,091 width=4) (actual time=0.017..24,890.103 rows=83,767,530 loops=1)

  • Heap Fetches: 0
9. 178,429.671 178,429.671 ↑ 1.1 83,784,773 1

Index Scan using idx_prospect_data_prospect_id on prospect_data pd (cost=0.57..6,149,936.90 rows=94,640,182 width=98) (actual time=0.012..178,429.671 rows=83,784,773 loops=1)

  • Filter: active
  • Rows Removed by Filter: 2,424,567
10. 0.011 4,974.853 ↑ 2,635,134,334.0 2 1

Materialize (cost=3,244.96..94,163,301.46 rows=5,270,268,668 width=930) (actual time=4,974.823..4,974.853 rows=2 loops=1)

11. 0.009 4,974.842 ↑ 2,635,134,334.0 2 1

Nested Loop Left Join (cost=3,244.96..80,987,629.79 rows=5,270,268,668 width=930) (actual time=4,974.817..4,974.842 rows=2 loops=1)

  • Join Filter: (s.call_id = c.call_id)
12. 0.005 4,974.776 ↑ 412,804.0 1 1

Nested Loop Left Join (cost=3,001.59..1,908,014.47 rows=412,804 width=734) (actual time=4,974.765..4,974.776 rows=1 loops=1)

  • Join Filter: (rv.call_id = c.call_id)
13. 0.007 4,958.839 ↑ 641.0 1 1

Nested Loop Left Join (cost=2,001.59..1,896,283.88 rows=641 width=722) (actual time=4,958.829..4,958.839 rows=1 loops=1)

14. 0.000 4,958.817 ↑ 641.0 1 1

Nested Loop Left Join (cost=2,001.31..1,895,138.78 rows=641 width=530) (actual time=4,958.808..4,958.817 rows=1 loops=1)

  • Join Filter: (rv_orig.call_id = c.call_id)
15. 0.005 4,817.055 ↑ 1.0 1 1

Nested Loop Left Join (cost=1,001.31..1,889,594.16 rows=1 width=526) (actual time=4,817.047..4,817.055 rows=1 loops=1)

16. 0.005 4,817.048 ↑ 1.0 1 1

Nested Loop Left Join (cost=1,001.03..1,889,585.85 rows=1 width=330) (actual time=4,817.041..4,817.048 rows=1 loops=1)

  • Join Filter: (qa.call_id = c.call_id)
17. 0.000 4,816.966 ↑ 1.0 1 1

Nested Loop (cost=1,000.61..1,889,577.39 rows=1 width=194) (actual time=4,816.960..4,816.966 rows=1 loops=1)

18. 2.738 4,817.022 ↑ 1.0 1 1

Gather Merge (cost=1,000.46..1,889,569.16 rows=1 width=52) (actual time=4,816.924..4,817.022 rows=1 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
19. 4,814.284 4,814.284 ↓ 0.0 0 3 / 3

Parallel Index Scan using idx_calls_prospect_id_2 on calls c (cost=0.44..1,888,569.03 rows=1 width=52) (actual time=4,140.341..4,814.284 rows=0 loops=3)

  • Filter: (call_id = 210,601,315)
  • Rows Removed by Filter: 7,830,550
20. 0.028 0.028 ↑ 1.0 1 1

Index Scan using pk_dispositions on dispositions dis (cost=0.14..8.16 rows=1 width=150) (actual time=0.027..0.028 rows=1 loops=1)

  • Index Cond: (disposition_id = c.disposition_id)
21. 0.077 0.077 ↑ 1.0 1 1

Index Scan using idx_qa_call_id on qa (cost=0.43..8.45 rows=1 width=144) (actual time=0.076..0.077 rows=1 loops=1)

  • Index Cond: (call_id = 210,601,315)
22. 0.002 0.002 ↓ 0.0 0 1

Index Scan using pk_actors on actors act2 (cost=0.28..8.29 rows=1 width=200) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: (actor_id = qa.lock_actor_id)
23. 2.177 141.789 ↓ 0.0 0 1

Gather (cost=1,000.00..5,536.61 rows=641 width=12) (actual time=141.755..141.789 rows=0 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
24. 139.612 139.612 ↓ 0.0 0 2 / 2

Parallel Seq Scan on qa_reviews rv_orig (cost=0.00..4,472.51 rows=377 width=12) (actual time=139.611..139.612 rows=0 loops=2)

  • Filter: (original AND (score IS NOT NULL) AND (call_id = 210,601,315))
  • Rows Removed by Filter: 129,692
25. 0.015 0.015 ↓ 0.0 0 1

Index Scan using pk_actors on actors act1 (cost=0.28..1.79 rows=1 width=200) (actual time=0.015..0.015 rows=0 loops=1)

  • Index Cond: (actor_id = rv_orig.actor_id)
26. 0.000 15.932 ↓ 0.0 0 1

Materialize (cost=1,000.00..5,540.13 rows=644 width=20) (actual time=15.932..15.932 rows=0 loops=1)

27. 1.996 15.951 ↓ 0.0 0 1

Gather (cost=1,000.00..5,536.91 rows=644 width=20) (actual time=15.921..15.951 rows=0 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
28. 13.955 13.955 ↓ 0.0 0 2 / 2

Parallel Seq Scan on qa_reviews rv (cost=0.00..4,472.51 rows=379 width=20) (actual time=13.955..13.955 rows=0 loops=2)

  • Filter: (newest AND (call_id = 210,601,315))
  • Rows Removed by Filter: 129,692
29. 0.016 0.057 ↑ 6,383.5 2 1

Materialize (cost=243.37..25,617.22 rows=12,767 width=212) (actual time=0.047..0.057 rows=2 loops=1)

30. 0.013 0.041 ↑ 6,383.5 2 1

Bitmap Heap Scan on sales s (cost=243.37..25,553.39 rows=12,767 width=212) (actual time=0.035..0.041 rows=2 loops=1)

  • Recheck Cond: (call_id = 210,601,315)
  • Heap Blocks: exact=1
31. 0.028 0.028 ↑ 6,383.5 2 1

Bitmap Index Scan on idx_sales_call_id (cost=0.00..240.18 rows=12,767 width=0) (actual time=0.028..0.028 rows=2 loops=1)

  • Index Cond: (call_id = 210,601,315)
32. 0.070 0.128 ↑ 3.4 140 1

Hash (cost=14.70..14.70 rows=470 width=128) (actual time=0.128..0.128 rows=140 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
33. 0.058 0.058 ↑ 3.4 140 1

Seq Scan on advertiser_units adv (cost=0.00..14.70 rows=470 width=128) (actual time=0.013..0.058 rows=140 loops=1)

34. 2.721 5.394 ↓ 5.1 7,198 1

Hash (cost=171.13..171.13 rows=1,413 width=204) (actual time=5.394..5.394 rows=7,198 loops=1)

  • Buckets: 8,192 (originally 2048) Batches: 1 (originally 1) Memory Usage: 459kB
35. 2.673 2.673 ↓ 5.1 7,198 1

Seq Scan on actors act (cost=0.00..171.13 rows=1,413 width=204) (actual time=0.008..2.673 rows=7,198 loops=1)

36. 0.033 0.670 ↑ 1.5 85 1

Hash (cost=11.30..11.30 rows=130 width=36) (actual time=0.670..0.670 rows=85 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
37. 0.637 0.637 ↑ 1.5 85 1

Seq Scan on qa_rooms2 qr2 (cost=0.00..11.30 rows=130 width=36) (actual time=0.615..0.637 rows=85 loops=1)

38. 0.038 0.075 ↑ 3.2 111 1

Hash (cost=13.50..13.50 rows=350 width=12) (actual time=0.075..0.075 rows=111 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
39. 0.037 0.037 ↑ 3.2 111 1

Seq Scan on marketing_groups mg (cost=0.00..13.50 rows=350 width=12) (actual time=0.008..0.037 rows=111 loops=1)

40. 0.008 0.017 ↑ 1.0 16 1

Hash (cost=1.16..1.16 rows=16 width=21) (actual time=0.017..0.017 rows=16 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
41. 0.009 0.009 ↑ 1.0 16 1

Seq Scan on brands b (cost=0.00..1.16 rows=16 width=21) (actual time=0.004..0.009 rows=16 loops=1)

Planning time : 15.793 ms
Execution time : 300,112.863 ms