Last active
February 3, 2019 23:28
-
-
Save schwern/146345bcc53beacb2d9eecff28fef3f6 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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