gistfile1.txt

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