explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vFGY

Settings
# exclusive inclusive rows x rows loops node
1. 2.024 4,562.913 ↓ 1.0 770 1

Hash Left Join (cost=13,290.76..8,284,091.17 rows=769 width=461) (actual time=131.011..4,562.913 rows=770 loops=1)

  • Hash Cond: (((c.domainid)::text = (st.domainid)::text) AND (c.storyid = st.id))
2. 1.445 140.307 ↓ 1.0 770 1

Hash Left Join (cost=13,282.47..16,540.16 rows=769 width=430) (actual time=123.612..140.307 rows=770 loops=1)

  • Hash Cond: (c.channelid = chan.id)
3. 1.110 138.848 ↓ 1.0 770 1

Hash Left Join (cost=13,253.34..16,502.81 rows=769 width=366) (actual time=123.585..138.848 rows=770 loops=1)

  • Hash Cond: (((c.domainid)::text = (ct.domainid)::text) AND (c.id = ct.campaignid))
4. 0.952 132.775 ↓ 1.0 770 1

Hash Left Join (cost=12,789.38..15,970.66 rows=769 width=344) (actual time=118.597..132.775 rows=770 loops=1)

  • Hash Cond: (ex.authorid = uu.id)
5. 3.508 130.665 ↓ 1.0 770 1

Hash Left Join (cost=12,721.83..15,892.54 rows=769 width=333) (actual time=117.419..130.665 rows=770 loops=1)

  • Hash Cond: ((c.exportmissionid)::text = (exp.id)::text)
6. 1.440 102.710 ↓ 1.0 770 1

Hash Left Join (cost=11,411.08..14,573.10 rows=769 width=325) (actual time=92.699..102.710 rows=770 loops=1)

  • Hash Cond: ((c.extractmissionid)::text = (exm.id)::text)
7. 0.799 75.813 ↓ 1.0 770 1

Hash Left Join (cost=10,067.62..13,220.45 rows=769 width=317) (actual time=66.982..75.813 rows=770 loops=1)

  • Hash Cond: (c.sandboxedby = us.id)
8. 0.904 73.848 ↓ 1.0 770 1

Hash Join (cost=10,000.07..13,149.57 rows=769 width=306) (actual time=65.792..73.848 rows=770 loops=1)

  • Hash Cond: (c.authorid = u.id)
9. 26.300 71.785 ↓ 1.0 770 1

Hash Right Join (cost=9,932.53..13,071.45 rows=769 width=295) (actual time=64.620..71.785 rows=770 loops=1)

  • Hash Cond: ((sco.id)::text = (c.scoremissionid)::text)
10. 28.359 28.359 ↑ 1.0 88,240 1

Seq Scan on scoremission sco (cost=0.00..2,793.74 rows=90,074 width=62) (actual time=0.004..28.359 rows=88,240 loops=1)

11. 0.574 17.126 ↓ 1.0 770 1

Hash (cost=9,922.91..9,922.91 rows=769 width=284) (actual time=17.126..17.126 rows=770 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 242kB
12. 1.468 16.552 ↓ 1.0 770 1

Merge Left Join (cost=9,875.78..9,922.91 rows=769 width=284) (actual time=15.439..16.552 rows=770 loops=1)

  • Merge Cond: (c.groupid = ex.id)
  • Join Filter: ((ex.domainid)::text = (c.domainid)::text)
13. 0.868 7.928 ↓ 1.0 770 1

Sort (cost=8,749.37..8,751.29 rows=769 width=272) (actual time=7.708..7.928 rows=770 loops=1)

  • Sort Key: c.groupid
  • Sort Method: quicksort Memory: 408kB
14. 1.338 7.060 ↓ 1.0 770 1

Bitmap Heap Scan on campaigns c (cost=5,466.73..8,712.51 rows=769 width=272) (actual time=5.810..7.060 rows=770 loops=1)

  • Recheck Cond: ((campaigndate >= '2019-05-01'::date) AND (campaigndate <= '2019-05-31'::date) AND ((domainid)::text = 'planet'::text))
  • Filter: (deletedby IS NULL)
  • Heap Blocks: exact=681
15. 0.391 5.722 ↓ 0.0 0 1

BitmapAnd (cost=5,466.73..5,466.73 rows=847 width=0) (actual time=5.721..5.722 rows=0 loops=1)

16. 0.718 0.718 ↓ 1.0 5,152 1

Bitmap Index Scan on campaignsday (cost=0.00..1,331.50 rows=5,108 width=0) (actual time=0.718..0.718 rows=5,152 loops=1)

  • Index Cond: ((campaigndate >= '2019-05-01'::date) AND (campaigndate <= '2019-05-31'::date))
17. 4.613 4.613 ↑ 1.0 14,473 1

Bitmap Index Scan on campaigns_pkey (cost=0.00..4,134.60 rows=14,906 width=0) (actual time=4.612..4.613 rows=14,473 loops=1)

  • Index Cond: ((domainid)::text = 'planet'::text)
18. 2.272 7.156 ↓ 1.1 3,954 1

Sort (cost=1,126.41..1,135.05 rows=3,458 width=27) (actual time=6.343..7.156 rows=3,954 loops=1)

  • Sort Key: ex.id
  • Sort Method: quicksort Memory: 368kB
19. 4.884 4.884 ↓ 1.0 3,477 1

Seq Scan on exports ex (cost=0.00..923.15 rows=3,458 width=27) (actual time=0.012..4.884 rows=3,477 loops=1)

  • Filter: ((domainid)::text = 'planet'::text)
  • Rows Removed by Filter: 28904
20. 0.632 1.159 ↑ 1.0 1,790 1

Hash (cost=45.02..45.02 rows=1,802 width=19) (actual time=1.159..1.159 rows=1,790 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 109kB
21. 0.527 0.527 ↑ 1.0 1,790 1

Seq Scan on users u (cost=0.00..45.02 rows=1,802 width=19) (actual time=0.006..0.527 rows=1,790 loops=1)

22. 0.616 1.166 ↑ 1.0 1,790 1

Hash (cost=45.02..45.02 rows=1,802 width=19) (actual time=1.165..1.166 rows=1,790 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 109kB
23. 0.550 0.550 ↑ 1.0 1,790 1

Seq Scan on users us (cost=0.00..45.02 rows=1,802 width=19) (actual time=0.009..0.550 rows=1,790 loops=1)

24. 14.482 25.457 ↑ 1.0 34,364 1

Hash (cost=913.76..913.76 rows=34,376 width=61) (actual time=25.456..25.457 rows=34,364 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3658kB
25. 10.975 10.975 ↑ 1.0 34,364 1

Seq Scan on extractmission exm (cost=0.00..913.76 rows=34,376 width=61) (actual time=0.004..10.975 rows=34,364 loops=1)

26. 13.816 24.447 ↓ 1.0 32,885 1

Hash (cost=899.78..899.78 rows=32,878 width=64) (actual time=24.447..24.447 rows=32,885 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3609kB
27. 10.631 10.631 ↓ 1.0 32,885 1

Seq Scan on exportmission exp (cost=0.00..899.78 rows=32,878 width=64) (actual time=0.008..10.631 rows=32,885 loops=1)

28. 0.610 1.158 ↑ 1.0 1,790 1

Hash (cost=45.02..45.02 rows=1,802 width=19) (actual time=1.158..1.158 rows=1,790 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 109kB
29. 0.548 0.548 ↑ 1.0 1,790 1

Seq Scan on users uu (cost=0.00..45.02 rows=1,802 width=19) (actual time=0.008..0.548 rows=1,790 loops=1)

30. 1.115 4.963 ↑ 1.0 2,741 1

Hash (cost=422.85..422.85 rows=2,741 width=37) (actual time=4.963..4.963 rows=2,741 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 203kB
31. 1.545 3.848 ↑ 1.0 2,741 1

Hash Left Join (cost=144.90..422.85 rows=2,741 width=37) (actual time=1.343..3.848 rows=2,741 loops=1)

  • Hash Cond: (ct.themeid = t.id)
32. 0.999 1.253 ↑ 1.0 2,741 1

Bitmap Heap Scan on campaignsthemes ct (cost=89.53..329.79 rows=2,741 width=19) (actual time=0.280..1.253 rows=2,741 loops=1)

  • Recheck Cond: ((domainid)::text = 'planet'::text)
  • Heap Blocks: exact=186
33. 0.254 0.254 ↑ 1.0 2,741 1

Bitmap Index Scan on unique_campaigns_themes (cost=0.00..88.84 rows=2,741 width=0) (actual time=0.254..0.254 rows=2,741 loops=1)

  • Index Cond: ((domainid)::text = 'planet'::text)
34. 0.574 1.050 ↓ 1.0 1,611 1

Hash (cost=35.72..35.72 rows=1,572 width=22) (actual time=1.049..1.050 rows=1,611 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 103kB
35. 0.476 0.476 ↓ 1.0 1,611 1

Seq Scan on themes t (cost=0.00..35.72 rows=1,572 width=22) (actual time=0.005..0.476 rows=1,611 loops=1)

36. 0.008 0.014 ↑ 121.4 7 1

Hash (cost=18.50..18.50 rows=850 width=68) (actual time=0.014..0.014 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.006 0.006 ↑ 121.4 7 1

Seq Scan on channels chan (cost=0.00..18.50 rows=850 width=68) (actual time=0.003..0.006 rows=7 loops=1)

38. 0.001 0.012 ↓ 0.0 0 1

Hash (cost=8.28..8.28 rows=1 width=37) (actual time=0.012..0.012 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
39. 0.011 0.011 ↓ 0.0 0 1

Index Scan using storydomainuniqueconstraint on stories st (cost=0.28..8.28 rows=1 width=37) (actual time=0.011..0.011 rows=0 loops=1)

  • Index Cond: ((domainid)::text = 'planet'::text)
40.          

SubPlan (forHash Left Join)

41. 3.080 4,420.570 ↑ 1.0 1 770

Aggregate (cost=10,751.01..10,751.02 rows=1 width=8) (actual time=5.740..5.741 rows=1 loops=770)

42. 6.160 4,417.490 ↓ 6.0 6 770

Result (cost=6,059.28..10,751.01 rows=1 width=0) (actual time=5.337..5.737 rows=6 loops=770)

  • One-Time Filter: (c.campaigndate <= '2019-05-31'::date)
43. 588.280 4,411.330 ↓ 6.0 6 770

Bitmap Heap Scan on campaigns (cost=6,059.28..10,751.01 rows=1 width=0) (actual time=5.332..5.729 rows=6 loops=770)

  • Recheck Cond: ((campaigndate >= '2019-05-01'::date) AND ((domainid)::text = 'planet'::text))
  • Filter: (groupid = c.groupid)
  • Rows Removed by Filter: 1058
  • Heap Blocks: exact=721490
44. 374.990 3,823.050 ↓ 0.0 0 770

BitmapAnd (cost=6,059.28..6,059.28 rows=1,238 width=0) (actual time=4.965..4.965 rows=0 loops=770)

45. 647.570 647.570 ↓ 1.0 7,519 770

Bitmap Index Scan on campaignsday (cost=0.00..1,924.43 rows=7,468 width=0) (actual time=0.841..0.841 rows=7,519 loops=770)

  • Index Cond: (campaigndate >= '2019-05-01'::date)
46. 2,800.490 2,800.490 ↑ 1.0 14,473 770

Bitmap Index Scan on campaigns_pkey (cost=0.00..4,134.60 rows=14,906 width=0) (actual time=3.637..3.637 rows=14,473 loops=770)

  • Index Cond: ((domainid)::text = 'planet'::text)
Planning time : 3.842 ms
Execution time : 4,564.626 ms