Skip to content

Instantly share code, notes, and snippets.

@ktosiek
Created June 1, 2017 21:19
Show Gist options
  • Save ktosiek/629946c47d62e75c0746a2f749173586 to your computer and use it in GitHub Desktop.
Save ktosiek/629946c47d62e75c0746a2f749173586 to your computer and use it in GitHub Desktop.
primary key vs unique not null
create table unique_test (
id serial primary key,
u integer unique not null, text text
);
insert into unique_test select i as u, i as text
from generate_series(7, 10007) as i;
# explain verbose select u, text from unique_test group by id;
QUERY PLAN
-------------------------------------------------------------------------------
HashAggregate (cost=170.01..270.02 rows=10001 width=40)
Output: u, text, id
Group Key: unique_test.id
-> Seq Scan on public.unique_test (cost=0.00..145.01 rows=10001 width=40)
Output: id, u, text
(5 rows)
# explain verbose select u, text from unique_test group by u, text;
QUERY PLAN
-------------------------------------------------------------------------------
HashAggregate (cost=195.01..295.02 rows=10001 width=36)
Output: u, text
Group Key: unique_test.u, unique_test.text
-> Seq Scan on public.unique_test (cost=0.00..145.01 rows=10001 width=36)
Output: id, u, text
(5 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment