![]() ![]() ![]() Postgres=# create table a (b integer unique not null, c text, d bigint) ![]() (Do note that UNIQUE columns can have multiple rows with NULLs). The answer? Sadly No! Furthermore, I went ahead and created a NOT NULL constraint, but that didn't change anything either. Let's check if we get the same optimization if we create a UNIQUE index on the column. Postgres=# create table a (b integer PRIMARY KEY, c text, d bigint) Having said that, if the same table is created with a PRIMARY KEY, we see that GROUP BY becomes smarter, in that we can see that the " Group Key" uses the Primary Key (here it is ' b') and correcty discards columns ' c' and ' d'. Postgres=# explain select b,c,d from a group by b,c,d Postgres=# explain select distinct b,c,d from a In particular, we're interested in the " Group Key" which is the same for both SQLs: On this table, we can see that SELECT * GROUP BY generates the exact same plan as SELECT DISTINCT. Postgres=# create table a (b integer, c text, d bigint) Ĭolumn | Type | Collation | Nullable | Default To highlight this difference, here I have an empty table with 3 columns: Unique) and where Postgres could do even better. We see a few scenarios where Postgres optimizes by removing unnecessary columns from the GROUP BY list (if a subset is already known to be This article aims at highlighting one aspect of how the query planner implementation of SELECT * GROUP BY differs from SELECT DISTINCT. (This came out of something I was trying out + discussing with Postgres enthusiasts - thanks to all for clarifying doubts) ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |