tag:blogger.com,1999:blog-8673752770146776575.post3558092929596108286..comments2024-01-02T05:04:05.147-08:00Comments on PostgreSQL and Databases in general: Parallelism, what next?Amit Kapilahttp://www.blogger.com/profile/01948926447381079550noreply@blogger.comBlogger16125tag:blogger.com,1999:blog-8673752770146776575.post-85028923206966547532020-09-10T15:42:56.875-07:002020-09-10T15:42:56.875-07:00That is unfortunate and the first time I have ever...That is unfortunate and the first time I have ever heard bad news from PG developers. Thank you and everyone else that has tried to tackle the big and hard problems that make a big difference. Hopefully in the future there might be a blog on whats left remaining and how others can help get it over the line as it seems like such a critical piece of work. Again, thank you and to everyone for their hard and amazing work on this and all things PG over the years.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8673752770146776575.post-14202311272172549142020-09-05T03:36:22.839-07:002020-09-05T03:36:22.839-07:00As far as I know nobody is actively working on it ...As far as I know nobody is actively working on it at this stage.Amit Kapilahttps://www.blogger.com/profile/01948926447381079550noreply@blogger.comtag:blogger.com,1999:blog-8673752770146776575.post-18571454176284399242020-09-05T02:51:43.287-07:002020-09-05T02:51:43.287-07:00I havent seen any update on zheap for a long while...I havent seen any update on zheap for a long while and github doesnt look active. Is the project still being actively worked on? and last I read PG14 might be earliest to be able to take it for a spin?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8673752770146776575.post-11996192931660550232020-02-27T14:14:48.539-08:002020-02-27T14:14:48.539-08:00Thanks for the insight. Would be a very useful fea...Thanks for the insight. Would be a very useful feature to have.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8673752770146776575.post-60669877020356125872020-02-27T03:59:15.674-08:002020-02-27T03:59:15.674-08:00To make them parallel, we need to first do some in...To make them parallel, we need to first do some infrastructure work like<br />a. change locking mechanism in some way so that parallel processes block each other for certain type of heavy-weight locks like relation extension lock and page lock. This can allow inserts.<br />b. For updates/deletes, I think we need to have shared combo CID hash, so that all participating processes know about them.<br />c. Then, we might need to do something about tuple locks depending on how we implement parallel update/deletes.<br /><br />After that, the actual work to make writes parallel might not be much.Amit Kapilahttps://www.blogger.com/profile/01948926447381079550noreply@blogger.comtag:blogger.com,1999:blog-8673752770146776575.post-72340751867314413552020-02-26T14:33:34.750-08:002020-02-26T14:33:34.750-08:00Any thoughts on parallel queries that write data (...Any thoughts on parallel queries that write data (Insert/Update)?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8673752770146776575.post-91282819304767232702020-02-26T06:57:36.606-08:002020-02-26T06:57:36.606-08:00This article is old but shows how to use skip scan...This article is old but shows how to use skip scan index search algorithm on any query. It has a side benefit that any query can be run in a minimum of two parallel processes. https://waa.ai/TsW0John Muddhttps://www.blogger.com/profile/02762302061633373290noreply@blogger.comtag:blogger.com,1999:blog-8673752770146776575.post-86856327247615501002020-02-22T05:07:57.069-08:002020-02-22T05:07:57.069-08:00I think it depends if the CTE can be inlined, then...I think it depends if the CTE can be inlined, then it can use parallelism for the entire statement. Based on your example, I have constructed a simple test and result is as below:<br />postgres=# Explain (Costs off) WITH a AS (select * from t1), b AS (Select * from t2) Select * from a, b;<br />QUERY PLAN<br />-------------------------------------<br />Gather<br />Workers Planned: 2<br />-> Nested Loop<br />-> Parallel Seq Scan on t1<br />-> Seq Scan on t2<br />(5 rows)<br />So such CTEs would use parallel plans and can parallelize the entire query. However if due to some reason, it can't inline the query, then the parallelism can be used only for the part of the statement. See below example:<br />postgres=# Explain (Costs off) WITH a AS Materialized (select * from t1), b AS Materialized (Select * from t2) Select * from a, b;<br />QUERY PLAN<br />---------------------------------------<br />Nested Loop<br />CTE a<br />-> Gather<br />Workers Planned: 2<br />-> Parallel Seq Scan on t1<br />CTE b<br />-> Seq Scan on t2<br />-> CTE Scan on a<br />-> CTE Scan on b<br />(9 rows)<br />The CTE scan itself can't be parallelized as of now.<br /><br />Does this help?Amit Kapilahttps://www.blogger.com/profile/01948926447381079550noreply@blogger.comtag:blogger.com,1999:blog-8673752770146776575.post-33496697777451195192020-02-21T10:59:24.875-08:002020-02-21T10:59:24.875-08:00I was thinking about this kind or parallelism:
WI...I was thinking about this kind or parallelism:<br /><br />WITH a (<br /> select from x<br />), b (<br /> select from y<br />), c (<br /> select from z<br />), d (<br /> select from a<br />), e (<br /> select from a,c<br />) /* f */<br />SELECT FROM e,d<br /><br />Thread1: a,d,<br />Thread2: b,-,<br />Thread3: c,e,f<br /><br />Is it useful? I think so:) <br />But is it possible?Artur Formellahttps://www.blogger.com/profile/06892656765202983075noreply@blogger.comtag:blogger.com,1999:blog-8673752770146776575.post-30016058923935222592020-02-19T21:16:04.846-08:002020-02-19T21:16:04.846-08:00Good point, Thomas!Good point, Thomas!Amit Kapilahttps://www.blogger.com/profile/01948926447381079550noreply@blogger.comtag:blogger.com,1999:blog-8673752770146776575.post-19232357495788460492020-02-19T20:59:36.640-08:002020-02-19T20:59:36.640-08:00Note that CTEs can finally be inlined in PostgreSQ...Note that CTEs can finally be inlined in PostgreSQL 12, which means that using WITH syntax doesn't necessarily prevent parallelism anymore! Parallelising materialised CTEs would require a bunch more machinery.Thomas Munrohttps://www.blogger.com/profile/08197999524704062001noreply@blogger.comtag:blogger.com,1999:blog-8673752770146776575.post-84239308140138748062020-02-19T20:59:06.372-08:002020-02-19T20:59:06.372-08:00This comment has been removed by the author.Thomas Munrohttps://www.blogger.com/profile/08197999524704062001noreply@blogger.comtag:blogger.com,1999:blog-8673752770146776575.post-60575664522046138182020-02-19T20:18:34.790-08:002020-02-19T20:18:34.790-08:00I could see that array_agg(...), json*_agg(...), j...I could see that array_agg(...), json*_agg(...), json*_object_agg(...) are marked as parallel safe. You can check by executing statement: select proname, proparallel from pg_proc where proname like 'array%'; and similarly for json functions. I see no reason for those to prevent a parallel plan. I checked these in HEAD. If you can share your exact query for count(distinct ...), I might be able to help better. Feel free to discuss such things on pgsql-hackers (https://www.postgresql.org/list/pgsql-hackers/) or other PG mailing list.Amit Kapilahttps://www.blogger.com/profile/01948926447381079550noreply@blogger.comtag:blogger.com,1999:blog-8673752770146776575.post-80047479660323299742020-02-19T19:46:28.804-08:002020-02-19T19:46:28.804-08:00The first point is CTE can contain DML statements ...The first point is CTE can contain DML statements like update/delete, so we won't be able to parallelise those as we still don't have parallelism for DML statements. Now, if the CTE contains read-only statements (Select queries), we can think of parallelising such statements, but I think this area would require more thoughts.Amit Kapilahttps://www.blogger.com/profile/01948926447381079550noreply@blogger.comtag:blogger.com,1999:blog-8673752770146776575.post-90186532014500351052020-02-19T10:08:39.997-08:002020-02-19T10:08:39.997-08:00What about parallel CTE? Each CTE node on the sep...What about parallel CTE? Each CTE node on the separate workerAnonymoushttps://www.blogger.com/profile/00088837841283306079noreply@blogger.comtag:blogger.com,1999:blog-8673752770146776575.post-56515751017268120462020-02-19T07:08:26.009-08:002020-02-19T07:08:26.009-08:00Thank you very much for the interesting article!
...Thank you very much for the interesting article!<br /><br />I think it would be another big step towards the goal of achieving more parallelism if the following aggregate functions became "parallel safe":<br /><br />array_agg(...)<br />json*_agg(...)<br />json*_object_agg(...)<br />count(distinct ...)<br /><br />I have seen several cases where these functions have prevented a parallel plan.Anonymousnoreply@blogger.com