Skip to content

Instantly share code, notes, and snippets.

@spetrunia
Created December 27, 2020 21:33
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save spetrunia/3033d080a105c14839c39ced51e8fb7f to your computer and use it in GitHub Desktop.
Save spetrunia/3033d080a105c14839c39ced51e8fb7f to your computer and use it in GitHub Desktop.
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