API tools faq
paste
Advertisement
DefconOne

yp.da.pr.7-2-7

DefconOne
Sep 19th, 2022
122
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PostgreSQL 5.30 KB | None | 0 0
raw download clone embed print report
  1. -- 1. mine:
  2.  
  3. EXPLAIN ANALYZE
  4. SELECT DISTINCT EXTRACT(WEEK FROM creation_date) week_number,
  5.        MAX(creation_date) OVER (PARTITION BY EXTRACT(WEEK FROM creation_date))
  6. FROM stackoverflow.posts pp
  7. JOIN (SELECT user_id,
  8.                          COUNT(id)
  9.                   FROM stackoverflow.posts
  10.                   GROUP BY 1
  11.                   ORDER BY 2 DESC
  12.                   LIMIT 1) tt ON pp.user_id = tt.user_id
  13.   AND DATE_TRUNC('month', creation_date)::date = '2008-10-01';
  14.  
  15. Unique (cost=13788.52..13788.53 rows=1 width=16) (actual time=109.211..113.660 rows=5 loops=1)
  16. -> Sort (cost=13788.52..13788.53 rows=1 width=16) (actual time=109.211..113.626 rows=440 loops=1)
  17. Sort Key: (date_part('week'::text, pp.creation_date)), (max(pp.creation_date) OVER (?))
  18. Sort Method: quicksort Memory: 45kB
  19. -> WindowAgg (cost=13788.49..13788.51 rows=1 width=16) (actual time=109.000..113.548 rows=440 loops=1)
  20. -> Sort (cost=13788.49..13788.50 rows=1 width=16) (actual time=108.980..113.415 rows=440 loops=1)
  21. Sort Key: (date_part('week'::text, pp.creation_date))
  22. Sort Method: quicksort Memory: 45kB
  23. -> Hash Join (cost=8412.70..13788.48 rows=1 width=16) (actual time=87.264..113.314 rows=440 loops=1)
  24. Hash Cond: (pp.user_id = tt.user_id)
  25. -> Gather (cost=1000.00..6372.58 rows=1219 width=12) (actual time=10.144..31.411 rows=63102 loops=1)
  26. Workers Planned: 2
  27. Workers Launched: 2
  28. -> Parallel Seq Scan on posts pp (cost=0.00..5250.68 rows=508 width=12) (actual time=6.403..19.387 rows=21034 loops=3)
  29. Filter: ((date_trunc('month'::text, creation_date))::date = '2008-10-01'::date)
  30. Rows Removed by Filter: 60231
  31. -> Hash (cost=7412.69..7412.69 rows=1 width=4) (actual time=77.027..77.030 rows=1 loops=1)
  32. Buckets: 1024 Batches: 1 Memory Usage: 9kB
  33. -> Subquery Scan on tt (cost=7412.67..7412.69 rows=1 width=4) (actual time=77.019..77.023 rows=1 loops=1)
  34. -> Limit (cost=7412.67..7412.68 rows=1 width=12) (actual time=77.016..77.018 rows=1 loops=1)
  35. -> Sort (cost=7412.67..7459.80 rows=18849 width=12) (actual time=77.014..77.016 rows=1 loops=1)
  36. Sort Key: (count(posts.id)) DESC
  37. Sort Method: top-N heapsort Memory: 25kB
  38. -> HashAggregate (cost=7129.94..7318.43 rows=18849 width=12) (actual time=72.184..75.242 rows=18849 loops=1)
  39. Group Key: posts.user_id
  40. -> Seq Scan on posts (cost=0.00..5910.96 rows=243796 width=8) (actual time=0.010..23.391 rows=243796 loops=1)
  41. Planning Time: 0.231 ms
  42. Execution Time: 113.873 ms
  43.  
  44. -- 2. not mine:
  45.  
  46. EXPLAIN ANALYZE
  47. WITH best_user AS
  48. (SELECT user_id,
  49.         COUNT(id) AS post_cnt
  50.         FROM stackoverflow.posts
  51.         GROUP BY user_id
  52.         ORDER BY post_cnt DESC
  53.         LIMIT 1),
  54. post_weekly AS
  55. (SELECT EXTRACT(WEEK FROM p.creation_date) AS week_nmb,
  56.        p.creation_date AS post_dt
  57. FROM stackoverflow.posts p
  58. JOIN best_user bu ON p.user_id=bu.user_id
  59. WHERE DATE_TRUNC('month', p.creation_date)::date='2008-10-01'
  60. GROUP BY week_nmb, p.creation_date
  61. ORDER BY p.creation_date)
  62. SELECT DISTINCT week_nmb,
  63.        LAST_VALUE(post_dt) OVER(PARTITION BY week_nmb ORDER BY post_dt ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  64. FROM post_weekly;
  65.  
  66. HashAggregate (cost=13789.95..13789.96 rows=1 width=24) (actual time=122.397..126.800 rows=5 loops=1)
  67. Group Key: post_weekly.week_nmb, last_value(post_weekly.post_dt) OVER (?)
  68. CTE best_user
  69. -> Limit (cost=7412.67..7412.68 rows=1 width=12) (actual time=81.892..81.894 rows=1 loops=1)
  70. -> Sort (cost=7412.67..7459.80 rows=18849 width=12) (actual time=81.890..81.892 rows=1 loops=1)
  71. Sort Key: (count(posts.id)) DESC
  72. Sort Method: top-N heapsort Memory: 25kB
  73. -> HashAggregate (cost=7129.94..7318.43 rows=18849 width=12) (actual time=76.946..80.090 rows=18849 loops=1)
  74. Group Key: posts.user_id
  75. -> Seq Scan on posts (cost=0.00..5910.96 rows=243796 width=8) (actual time=0.013..25.162 rows=243796 loops=1)
  76. CTE post_weekly
  77. -> Group (cost=6377.21..6377.22 rows=1 width=16) (actual time=121.883..126.389 rows=440 loops=1)
  78. Group Key: p.creation_date, (date_part('week'::text, p.creation_date))
  79. -> Sort (cost=6377.21..6377.21 rows=1 width=16) (actual time=121.880..126.305 rows=440 loops=1)
  80. Sort Key: p.creation_date, (date_part('week'::text, p.creation_date))
  81. Sort Method: quicksort Memory: 45kB
  82. -> Hash Join (cost=1000.03..6377.20 rows=1 width=16) (actual time=94.096..126.181 rows=440 loops=1)
  83. Hash Cond: (p.user_id = bu.user_id)
  84. -> Gather (cost=1000.00..6372.58 rows=1219 width=12) (actual time=12.049..38.862 rows=63102 loops=1)
  85. Workers Planned: 2
  86. Workers Launched: 2
  87. -> Parallel Seq Scan on posts p (cost=0.00..5250.68 rows=508 width=12) (actual time=7.209..23.672 rows=21034 loops=3)
  88. Filter: ((date_trunc('month'::text, creation_date))::date = '2008-10-01'::date)
  89. Rows Removed by Filter: 60231
  90. -> Hash (cost=0.02..0.02 rows=1 width=4) (actual time=81.902..81.903 rows=1 loops=1)
  91. Buckets: 1024 Batches: 1 Memory Usage: 9kB
  92. -> CTE Scan on best_user bu (cost=0.00..0.02 rows=1 width=4) (actual time=81.896..81.897 rows=1 loops=1)
  93. -> WindowAgg (cost=0.03..0.05 rows=1 width=24) (actual time=122.153..122.324 rows=440 loops=1)
  94. -> Sort (cost=0.03..0.04 rows=1 width=16) (actual time=122.140..122.163 rows=440 loops=1)
  95. Sort Key: post_weekly.week_nmb, post_weekly.post_dt
  96. Sort Method: quicksort Memory: 45kB
  97. -> CTE Scan on post_weekly (cost=0.00..0.02 rows=1 width=16) (actual time=121.885..122.090 rows=440 loops=1)
  98. Planning Time: 0.227 ms
  99. Execution Time: 127.054 ms
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!