5 ms·
> Queries using SELECT DISTINCT can now be executed in parallel. This sounds quite interesting, but I would assume it does not always work? I didn't see this m
by chrstr 4y ago
> Queries using SELECT DISTINCT can now be executed in parallel.
This sounds quite interesting, but I would assume it does not always work? I didn't see this mentioned in the linked documentation, does someone know when/how the parallel distinct works?
- cogman10 4y agoCouldn't tell you the when, but I can tell you the how is likely how you'd expect. Generally speaking, to do distinct you need a dictionary to look up previously seen values. To do it in parallel you need to make that dictionary thread safe. For Java, such a thread safe dictionary is made by segmenting the table and synchronizing on the segments. So you'd hash your values, figure out which segment that targets, lock that segment, and then read/update that segment to contain the new value. I'd assume that postgres is doing a fairly similar trick, The only additional synchronization would be on a linked list of found values. In that case, you could either lock the list and update as new values come in, you could sort those values after the fact, or you could employ a lock free algorithm to add nodes to the list (see lock free queue implementations).
- tpetry 4y agoMost parallel operations in PG are implemented by simple merge the dataset, work independently and merge the results. I expect the new distinct to behave the same and not work on a shared data structure.
- jeltz 4y agoYou are correct: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=22c4e88ebff408acd52e212543a77158bde59e69 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...
- chrstr 4y agoThanks! May be helpful to include this in the documentation, since I guess it will then often depend on the numDistinctRows estimate [1] if the parallel plan is used. [1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/optimizer/plan/planner.c;h=468105d91ea71dc195aae1ed12ab3a2044128ca2;hb=refs/heads/REL_15_STABLE#l4420 https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f...
- riku_iki 4y ago"select distinct" is likely now syntax sugar around "select ... group by 1", which worked in parallel for a while.