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) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment