Analyze distinct and group by in PostgreSQL
This article introduces the relevant knowledge of "analyzing distinct and group by in PostgreSQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!
Generally speaking, to obtain a unique value, either distinct or group by can be used, but when there is a primary key, group by will optimize it to reduce multiple grouping keys to primary keys.
The absence of a primary key
[pg12@localhost] $psqlExpanded display is used automatically.psql (12.2) Type "help" for help. [local:/data/run/pg12]: 5120 pg12@testdb=# create table tbl1 (id int,c1 text,c2 int,c3 varchar); CREATE TABLE [local:/data/run/pg12]: 5120 pg12@testdb=# insert into tbl1 (id,c1,c2,c3) select x | |'c1 'from generate_series (1m 100000) as x | INSERT 0 100000 [local:/data/run/pg12]: 5120 pg12@testdb=# explain select distinct id,c1,c2,c3 from tbl1 QUERY PLAN-HashAggregate (cost=1668.94..1720.54 rows=5160 width=72) Group Key: id, C1, c2 C3-> Seq Scan on tbl1 (cost=0.00..1152.97 rows=51597 width=72) (3 rows) [local:/data/run/pg12]: 5120 pg12@testdb=# explain select id,c1,c2,c3 from tbl1 group by id,c1,c2,c3 QUERY PLAN-HashAggregate (cost=1668.94..1720.54 rows=5160 width=72) Group Key: id, C1, c2 C3-> Seq Scan on tbl1 (cost=0.00..1152.97 rows=51597 width=72) (3 rows)
In the case of a primary key
[local:/data/run/pg12]: 5120 pg12@testdb=# alter table tbl1 add primary key (id); 'ALTER TABLE [local:/data/run/pg12]: 5120 pg12@testdb=# explain select distinct id,c1,c2,c3 from tbl1 QUERY PLAN-Unique (cost=14043.82..15293.82 rows=100000 width=72) -> Sort (cost=14043.82..14293.82 rows=100000 width=72) Sort Key: id C1 cost=0.00..1637.00 rows=100000 width=72 c2 c3-> Seq Scan on tbl1 (cost=0.00..1637.00 rows=100000 width=72) (4 rows) [local:/data/run/pg12]: 5120 pg12@testdb=# explain select id,c1,c2,c3 from tbl1 group by id,c1,c2,c3 QUERY PLAN -Group (cost=0.29..5402.29 rows=100000 width=72) Group Key: id-> Index Scan using tbl1_pkey on tbl1 (cost=0.29..5152.29 rows=100000 width=72) (3 rows) [local:/data/run/pg12]: 5120 pg12@testdb=#
In the case of a primary key, when using group by, the grouping key only needs the primary key.
This is the end of "analyzing distinct and group by in PostgreSQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!
