From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andreas Joseph Krogh <andreas@visena.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: How to eliminate extra "NOT EXISTS"-query here?
Date: Sat, 25 Nov 2023 11:08:28 -0500
Message-ID: <3486076.1700928508@sss.pgh.pa.us> (raw)
In-Reply-To: <VisenaEmail.39.cd03fed9d5bd1deb.18c06a48a33@origo-test01.app.internal.visena.net>
References: <VisenaEmail.39.cd03fed9d5bd1deb.18c06a48a33@origo-test01.app.internal.visena.net>
Andreas Joseph Krogh <andreas@visena.com> 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 = t.id)
> OR NOT EXISTS (
> select * from stuff s where s.test_id = 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="d"
> returned as well, but in order to do that I need to execute the “or not
> exists”-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_id) ss
on ss.test_id = 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: tgl@sss.pgh.pa.us, andreas@visena.com
Subject: Re: How to eliminate extra "NOT EXISTS"-query here?
In-Reply-To: <3486076.1700928508@sss.pgh.pa.us>
References: <VisenaEmail.39.cd03fed9d5bd1deb.18c06a48a33@origo-test01.app.internal.visena.net> <3486076.1700928508@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox