Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
test=> select count(*) from orders;
count
---------
1003116
(1 row)
test=> select count(distinct articleid) from orders;
count
--------
999921
(1 row)
test=> explain analyze SELECT articleID FROM orders
GROUP BY articleID
ORDER BY COUNT(articleID) DESC LIMIT 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=49033.06..49033.06 rows=1 width=12) (actual time=539.011..539.011 rows=1 loops=1)
-> Sort (cost=49033.06..51284.44 rows=900554 width=12) (actual time=539.010..539.010 rows=1 loops=1)
Sort Key: (count(articleid)) DESC
Sort Method: top-N heapsort Memory: 25kB
-> GroupAggregate (cost=0.42..44530.29 rows=900554 width=12) (actual time=0.690..431.937 rows=999921 loops=1)
Group Key: articleid
-> Index Only Scan using orders_article_id on orders (cost=0.42..30509.17 rows=1003116 width=4) (actual time=0.065..166.622 rows=1003116 loops=1)
Heap Fetches: 1003116
Planning time: 0.133 ms
Execution time: 539.053 ms
test=> explain analyze WITH T AS(
SELECT COUNT(*) AS C1, articleID FROM orders
GROUP BY articleID)
SELECT articleID FROM T
WHERE C1 = (SELECT MAX(C1) FROM T);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
CTE Scan on t (cost=64792.76..85055.23 rows=4503 width=4) (actual time=808.705..888.546 rows=1 loops=1)
Filter: (c1 = $1)
Rows Removed by Filter: 999920
CTE t
-> GroupAggregate (cost=0.42..44530.29 rows=900554 width=12) (actual time=0.743..439.958 rows=999921 loops=1)
Group Key: orders.articleid
-> Index Only Scan using orders_article_id on orders (cost=0.42..30509.17 rows=1003116 width=4) (actual time=0.029..177.342 rows=1003116 loops=1)
Heap Fetches: 1003116
InitPlan 2 (returns $1)
-> Aggregate (cost=20262.47..20262.48 rows=1 width=8) (actual time=807.957..807.957 rows=1 loops=1)
-> CTE Scan on t t_1 (cost=0.00..18011.08 rows=900554 width=8) (actual time=0.001..716.844 rows=999921 loops=1)
Planning time: 0.181 ms
Execution time: 891.251 ms
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment