explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kOUG

Settings
# exclusive inclusive rows x rows loops node
1. 0.840 0.840 ↑ 64.8 73 1

CTE Scan on finalinsights (cost=1,562.50..1,657.16 rows=4,733 width=64) (actual time=0.797..0.840 rows=73 loops=1)

2.          

CTE insightswithpriority

3. 0.095 0.400 ↑ 87.1 105 1

HashAggregate (cost=357.81..517.92 rows=9,149 width=49) (actual time=0.357..0.400 rows=105 loops=1)

  • Group Key: insights.insightid, uipriority.prioritisedscore, cp.preferencetype, categories.parentid, uipriority.userid, pcp.preferencetype
4. 0.038 0.305 ↑ 87.1 105 1

Hash Left Join (cost=132.71..220.58 rows=9,149 width=41) (actual time=0.137..0.305 rows=105 loops=1)

  • Hash Cond: ((cp.userid = pcp.userid) AND (categories.parentid = pcp.categoryid))
5. 0.037 0.259 ↑ 87.1 105 1

Hash Left Join (cost=81.18..100.42 rows=9,149 width=47) (actual time=0.116..0.259 rows=105 loops=1)

  • Hash Cond: ((uipriority.userid = cp.userid) AND (categories.categoryid = cp.categoryid))
6. 0.025 0.211 ↓ 1.0 105 1

Hash Join (cost=29.66..47.09 rows=103 width=45) (actual time=0.092..0.211 rows=105 loops=1)

  • Hash Cond: (insights.sourcetypeid = st.sourcetypeid)
7. 0.026 0.182 ↓ 1.0 105 1

Hash Join (cost=7.96..23.98 rows=103 width=53) (actual time=0.084..0.182 rows=105 loops=1)

  • Hash Cond: (insights.categoryid = categories.categoryid)
8. 0.030 0.140 ↓ 1.0 105 1

Hash Join (cost=6.57..21.18 rows=103 width=37) (actual time=0.061..0.140 rows=105 loops=1)

  • Hash Cond: (insights.insightid = uipriority.insightid)
9. 0.063 0.063 ↑ 1.6 152 1

Seq Scan on insights (cost=0.00..12.66 rows=244 width=24) (actual time=0.007..0.063 rows=152 loops=1)

  • Filter: (expirydate >= now())
  • Rows Removed by Filter: 2
10. 0.018 0.047 ↓ 1.0 106 1

Hash (cost=5.29..5.29 rows=103 width=21) (actual time=0.047..0.047 rows=106 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
11. 0.029 0.029 ↓ 1.0 106 1

Seq Scan on userinsightpriority uipriority (cost=0.00..5.29 rows=103 width=21) (actual time=0.005..0.029 rows=106 loops=1)

  • Filter: (userid = 3)
  • Rows Removed by Filter: 20
12. 0.005 0.016 ↑ 1.0 17 1

Hash (cost=1.17..1.17 rows=17 width=16) (actual time=0.016..0.016 rows=17 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.011 0.011 ↑ 1.0 17 1

Seq Scan on categories (cost=0.00..1.17 rows=17 width=16) (actual time=0.007..0.011 rows=17 loops=1)

14. 0.003 0.004 ↑ 260.0 2 1

Hash (cost=15.20..15.20 rows=520 width=8) (actual time=0.004..0.004 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.001 0.001 ↑ 260.0 2 1

Seq Scan on sourcetypes st (cost=0.00..15.20 rows=520 width=8) (actual time=0.001..0.001 rows=2 loops=1)

16. 0.005 0.011 ↑ 125.8 12 1

Hash (cost=28.88..28.88 rows=1,510 width=18) (actual time=0.011..0.011 rows=12 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
17. 0.006 0.006 ↑ 125.8 12 1

Seq Scan on usercategorypreferences cp (cost=0.00..28.88 rows=1,510 width=18) (actual time=0.005..0.006 rows=12 loops=1)

  • Filter: (userid = 3)
18. 0.003 0.008 ↑ 125.8 12 1

Hash (cost=28.88..28.88 rows=1,510 width=18) (actual time=0.008..0.008 rows=12 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
19. 0.005 0.005 ↑ 125.8 12 1

Seq Scan on usercategorypreferences pcp (cost=0.00..28.88 rows=1,510 width=18) (actual time=0.003..0.005 rows=12 loops=1)

  • Filter: (userid = 3)
20.          

CTE insightswithentitycount

21. 0.058 0.709 ↑ 64.8 73 1

HashAggregate (cost=483.52..530.85 rows=4,733 width=56) (actual time=0.693..0.709 rows=73 loops=1)

  • Group Key: ia.insightid, ip.prioritisedscore, ip.categorypriority, ip.subcategorypreference
22. 0.025 0.651 ↑ 55.0 86 1

Hash Left Join (cost=33.89..424.36 rows=4,733 width=48) (actual time=0.475..0.651 rows=86 loops=1)

  • Hash Cond: ((ia.entityid = uop.orgunitid) AND (ip.userid = uop.userid))
  • Join Filter: (et.type = 'orgunit'::text)
  • Rows Removed by Join Filter: 1
  • Filter: ((ucp.preferencetype = 1) OR (uop.preferencetype = 1))
  • Rows Removed by Filter: 26
23. 0.048 0.618 ↑ 49.4 112 1

Hash Left Join (cost=32.59..381.33 rows=5,535 width=98) (actual time=0.458..0.618 rows=112 loops=1)

  • Hash Cond: ((ia.entityid = ucp.customerid) AND (ip.userid = ucp.userid))
  • Join Filter: (et.type = 'customer'::text)
24. 0.030 0.558 ↑ 49.4 112 1

Hash Join (cost=31.01..337.96 rows=5,535 width=96) (actual time=0.438..0.558 rows=112 loops=1)

  • Hash Cond: (ip.insightid = ia.insightid)
25. 0.454 0.454 ↑ 87.1 105 1

CTE Scan on insightswithpriority ip (cost=0.00..182.98 rows=9,149 width=56) (actual time=0.358..0.454 rows=105 loops=1)

26. 0.023 0.074 ↓ 1.0 123 1

Hash (cost=29.50..29.50 rows=121 width=48) (actual time=0.074..0.074 rows=123 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
27. 0.031 0.051 ↓ 1.0 123 1

Hash Join (cost=24.62..29.50 rows=121 width=48) (actual time=0.015..0.051 rows=123 loops=1)

  • Hash Cond: (ia.entitytypeid = et.entitytypeid)
28. 0.014 0.014 ↓ 1.0 123 1

Seq Scan on insightassociations ia (cost=0.00..3.21 rows=121 width=24) (actual time=0.004..0.014 rows=123 loops=1)

29. 0.005 0.006 ↑ 216.7 3 1

Hash (cost=16.50..16.50 rows=650 width=40) (actual time=0.006..0.006 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 0.001 0.001 ↑ 216.7 3 1

Seq Scan on entitytypes et (cost=0.00..16.50 rows=650 width=40) (actual time=0.001..0.001 rows=3 loops=1)

31. 0.003 0.012 ↓ 1.1 25 1

Hash (cost=1.23..1.23 rows=23 width=18) (actual time=0.012..0.012 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
32. 0.009 0.009 ↓ 1.1 25 1

Seq Scan on usercustomerpreferences ucp (cost=0.00..1.23 rows=23 width=18) (actual time=0.003..0.009 rows=25 loops=1)

33. 0.005 0.008 ↑ 1.0 12 1

Hash (cost=1.12..1.12 rows=12 width=18) (actual time=0.008..0.008 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
34. 0.003 0.003 ↑ 1.0 12 1

Seq Scan on userorgunitpreferences uop (cost=0.00..1.12 rows=12 width=18) (actual time=0.003..0.003 rows=12 loops=1)

35.          

CTE finalinsights

36. 0.027 0.821 ↑ 64.8 73 1

WindowAgg (cost=383.58..513.73 rows=4,733 width=64) (actual time=0.795..0.821 rows=73 loops=1)

37. 0.061 0.794 ↑ 64.8 73 1

Sort (cost=383.58..395.41 rows=4,733 width=56) (actual time=0.790..0.794 rows=73 loops=1)

  • Sort Key: insightswithentitycount.subcategorypreference, insightswithentitycount.categorypriority, insightswithentitycount.entitycount DESC, insightswithentitycount.prioritisedscore DESC, insightswithentitycount.insightid
  • Sort Method: quicksort Memory: 30kB
38. 0.733 0.733 ↑ 64.8 73 1

CTE Scan on insightswithentitycount (cost=0.00..94.66 rows=4,733 width=56) (actual time=0.693..0.733 rows=73 loops=1)

Planning time : 1.434 ms
Execution time : 1.163 ms