Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r6vCv-0039lj-Pn for pgsql-general@arkaria.postgresql.org; Sat, 25 Nov 2023 16:08:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1r6vCu-003HLe-A0 for pgsql-general@arkaria.postgresql.org; Sat, 25 Nov 2023 16:08:36 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r6vCt-003HLW-UW for pgsql-general@lists.postgresql.org; Sat, 25 Nov 2023 16:08:35 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r6vCn-007JgO-QZ for pgsql-general@lists.postgresql.org; Sat, 25 Nov 2023 16:08:34 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 3APG8She3486077; Sat, 25 Nov 2023 11:08:28 -0500 From: Tom Lane To: Andreas Joseph Krogh cc: pgsql-general@lists.postgresql.org Subject: Re: How to eliminate extra "NOT EXISTS"-query here? In-reply-to: References: Comments: In-reply-to Andreas Joseph Krogh message dated "Sat, 25 Nov 2023 14:26:56 +0100" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <3486075.1700928508.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Sat, 25 Nov 2023 11:08:28 -0500 Message-ID: <3486076.1700928508@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Andreas Joseph Krogh writes: > -- This works, but I'd rather not do the extra EXISTS > select * from test t > WHERE (NOT ARRAY ['x', 'y', 'z', 't']::varchar[] <@ (select array_agg(s.= v) from > stuffs WHERE s.test_id =3D t.id) > OR NOT EXISTS ( > select * from stuff s where s.test_id =3D t.id > ) > ) > ; > So, I want to return all entries in test not having any of ARRAY ['x', '= y', = > 'z', 't'] referenced in the table stuff, and I'd like to have test.id=3D= "d" = > returned as well, but in order to do that I need to execute the =E2=80=9C= or not = > exists=E2=80=9D-query. Is it possible to avoid that? Probably not directly, but perhaps you could improve the performance of this query by converting the sub-selects into a left join: select * from test t left join (select s.test_id, array_agg(s.v) as arr from stuffs group by s.test_i= d) ss on ss.test_id =3D t.id WHERE (NOT ARRAY ['x', 'y', 'z', 't']::varchar[] <@ ss.arr) OR ss.test_id IS NULL; Another possibility is ... WHERE (ARRAY ['x', 'y', 'z', 't']::varchar[] <@ ss.arr) IS NOT TRUE but I don't think that's more readable really, and it will save little. In either case, this would result in computing array_agg once for each group of test_id values in "stuffs", while your original computes a similar aggregate for each row in "test". So whether this is better depends on the relative sizes of the tables, although my proposal avoids random access to "stuffs" so it will have some advantage. regards, tom lane