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=# 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