gistfile1.txt

test=# CREATE TABLE t1 (p int NOT NULL, a int NOT NULL, PRIMARY KEY (p,a));
CREATE TABLE
test=# insert into t1 select 2,generate_series from generate_series(1, 1000000);
INSERT 0 1000000
test=# analyze t1;
ANALYZE
test=# explain analyze select MIN(a) from t1 where p = 2 group by p;
                                                             QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
 Finalize GroupAggregate  (cost=1000.00..12716.90 rows=1 width=8) (actual time=85.409..85.409 rows=1 loops=1)
   Group Key: p
   ->  Gather  (cost=1000.00..12716.88 rows=2 width=8) (actual time=85.317..86.190 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial GroupAggregate  (cost=0.00..11716.68 rows=1 width=8) (actual time=83.664..83.665 rows=1 loops=3)
               Group Key: p
               ->  Parallel Seq Scan on t1  (cost=0.00..9633.33 rows=416667 width=8) (actual time=0.012..45.384 rows=333333 loops=3)
                     Filter: (p = 2)
 Planning Time: 0.116 ms
 Execution Time: 86.224 ms
(11 rows)

test=# explain analyze select MIN(a) from t1 where p = 2;
                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.47..0.48 rows=1 width=4) (actual time=0.033..0.034 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.42..0.47 rows=1 width=4) (actual time=0.031..0.031 rows=1 loops=1)
           ->  Index Only Scan using t1_pkey on t1  (cost=0.42..41214.93 rows=1000000 width=4) (actual time=0.030..0.030 rows=1 loops=1)
                 Index Cond: ((p = 2) AND (a IS NOT NULL))
                 Heap Fetches: 1
 Planning Time: 0.071 ms
 Execution Time: 0.046 ms
(8 rows)

test=# explain analyze select MIN(a) from t1 group by p;
                                                                QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 Finalize GroupAggregate  (cost=11675.05..11675.30 rows=1 width=8) (actual time=117.737..117.737 rows=1 loops=1)
   Group Key: p
   ->  Gather Merge  (cost=11675.05..11675.28 rows=2 width=8) (actual time=117.726..118.535 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort  (cost=10675.02..10675.03 rows=1 width=8) (actual time=116.534..116.534 rows=1 loops=3)
               Sort Key: p
               Sort Method: quicksort  Memory: 25kB
               Worker 0:  Sort Method: quicksort  Memory: 25kB
               Worker 1:  Sort Method: quicksort  Memory: 25kB
               ->  Partial HashAggregate  (cost=10675.00..10675.01 rows=1 width=8) (actual time=116.512..116.517 rows=1 loops=3)
                     Group Key: p
                     Peak Memory Usage: 53 kB
                     ->  Parallel Seq Scan on t1  (cost=0.00..8591.67 rows=416667 width=8) (actual time=0.010..28.787 rows=333333 loops=3)
 Planning Time: 0.038 ms
 Execution Time: 118.568 ms
(16 rows)