Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pYY3d-000840-Ql for pgsql-general@arkaria.postgresql.org; Sat, 04 Mar 2023 20:00:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pYY3c-0003yQ-4W for pgsql-general@arkaria.postgresql.org; Sat, 04 Mar 2023 20:00:40 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pYY3b-0003yG-Pp for pgsql-general@lists.postgresql.org; Sat, 04 Mar 2023 20:00:39 +0000 Received: from evolvis.org ([217.144.135.11]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pYY3Z-0003AM-JX for pgsql-general@lists.postgresql.org; Sat, 04 Mar 2023 20:00:39 +0000 Received: from x61w.mirbsd.org (xdsl-213-196-244-135.nc.de [213.196.244.135]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (Client did not present a certificate) by evolvis.org (Postfix) with ESMTPSA id 094F7100059; Sat, 4 Mar 2023 20:00:35 +0000 (UTC) Received: by x61w.mirbsd.org (Postfix, from userid 1000) id A1CE5624D8; Sat, 4 Mar 2023 21:00:33 +0100 (CET) Received: from localhost (localhost [127.0.0.1]) by x61w.mirbsd.org (Postfix) with ESMTP id 9B78E60495; Sat, 4 Mar 2023 21:00:33 +0100 (CET) Date: Sat, 4 Mar 2023 21:00:33 +0100 (CET) From: Thorsten Glaser To: Alban Hertroys cc: pgsql-general@lists.postgresql.org Subject: Re: DISTINCT *and* ORDER BY in aggregate functions on expressions(!)y In-Reply-To: <82E2D483-67A9-480D-8671-8937D077840B@gmail.com> Message-ID: <4f11e322-31cf-fc3d-86aa-401a3bf9d86@evolvis.org> References: <23d08470-aaa8-afca-dfd7-37261487a1f8@evolvis.org> <559416.1677547978@sss.pgh.pa.us> <9fc8e12b-f943-c633-a37-254915f819a0@evolvis.org> <9da4cb5a-81bb-23a4-8261-6ba8a29579af@evolvis.org> <5AF0C3F0-4D5A-4198-B58B-6E7D4994969B@gmail.com> <9e86b053-a29a-a82-78f0-1228a391edc3@evolvis.org> <82E2D483-67A9-480D-8671-8937D077840B@gmail.com> Content-Language: de-DE-1901 MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: QUOTED-PRINTABLE List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Sat, 4 Mar 2023, Alban Hertroys wrote: >> But isn=E2=80=99t that the same as with a regular LEFT JOIN? > >Similar, but not the same, I=E2=80=99d say. > >I do now notice that I made some copying errors there, I was a bit >nauseous at that time. np, I=E2=80=99m under pollen attack currently so also not at my best. >That should have read: > >>> select jsonb_build_object('opening_times=E2=80=99, >>> jsonb_agg(obj >>> =09ORDER BY >>> =09 obj->>'weekday=E2=80=99, >>> =09 obj->>'from_hour=E2=80=99, >>> =09 obj->>'to_hour') >>> ) >>> from cot >>> cross join lateral jsonb_build_object( >>> =09=09'weekday', cot.weekday, >>> =09=09'from_hour', cot.from_hour, >>> =09=09'to_hour', cot.to_hour) obj > >The lateral join applies the function to each row returned from the >left side of the join and enriches that row with the function result. I >used a cross join because there is no join condition to apply to the >lateral, otherwise you could also use an inner join on true. Okay, so I got it now. But that=E2=80=99s still identical to the LEFT JOIN which I=E2=80=99m using in that example, because=E2=80=A6 >A left join wouldn=E2=80=99t make much sense here, unless the function cou= ld =E2=80=A6 I have a LEFT JOIN already and can just use the CTE there, so I don=E2=80=99t have to add an extra lateral join. But good to know for the future/when I don=E2=80=99t have that. >return NULL - for example if it were a function marked as STRICT and >some of the input parameter values (from the table) could be NULL. OK. >You need a sub-select, which in turn creates its own result set. It=E2=80= =99s >up to the planner whether the left or the right side gets executed >first, after which the results of the other side of the join get merged >to this, or whether this can all be collected in one go. That=E2=80=99s up= to >the query planner to decide though, and it could be right. OK, but that shouldn=E2=80=99t make a difference here as it needs to run ov= er all rows of the cgwa table anyway (possibly reduced by filtering on users). While not the case here, I see that for other entries the lateral join would cause more work: for the =E2=80=9Cqualification=E2=80=9D kinds o= f tables, for example, the individual qualification table has very few rows (these are entered by the site admin), but the m:n connection table (whatever the correct name for these is) has a lot because many of the users have many of these qualifications. If I use a CTE to add a JSON object to the individual qualification table first, it doesn=E2=80= =99t run on each qualification multiple times; if I use a lateral join, it possibly, modulo planner optimisations, runs the jsonb_build_object function many times per qualification despite them all giving the same result. And, even if the optimisations catch that, it=E2=80=99s mentally no= t the same. >In my experience, lateral joins go well with the jsonb functions. They >tend to reduce code repetition when referencing object members, such as >in your case. Right. In my case I can get the same by adding a CTE instead though, and it=E2=80=99s hard to see which is better, performance-wise. This is a lot to take in, and I r=CC=B2e=CC=B2a=CC=B2l=CC=B2l=CC=B2y=CC=B2 = appreciate the detailed explanations given alongside =E2=98=BB bye, //mirabilos --=20 15:41=E2=8E=9C Somebody write a testsuite for hello= world :-)