pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How to eliminate extra "NOT EXISTS"-query here?
6+ messages / 4 participants
[nested] [flat]

* How to eliminate extra "NOT EXISTS"-query here?
@ 2023-11-25 13:26 Andreas Joseph Krogh <andreas@visena.com>
  2023-11-25 16:08 ` Re: How to eliminate extra "NOT EXISTS"-query here? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: Andreas Joseph Krogh @ 2023-11-25 13:26 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org



Hi, I'm testing if some dataset contains an array of elements and want to 
return all “not containing the specified array”, including entries in master 
table not being referenced.



I have the following schema:

drop table if exists stuff;
drop table if exists test;
CREATE TABLE test(
id varchar primary key
);

create table stuff(
id serial primary key,
test_id varchar NOT NULL REFERENCES test(id),
v varchar not null,
unique (test_id, v)
);

INSERT INTO test(id) values ('a');
INSERT INTO test(id) values ('b');
INSERT INTO test(id) values ('c');
INSERT INTO test(id) values ('d');

INSERT INTO stuff(test_id, v)
values ('a', 'x')
;

INSERT INTO stuff(test_id, v)
values ('b', 'x')
 , ('b', 'y')
;

INSERT INTO stuff(test_id, v)
values ('c', 'x')
 , ('c', 'y')
 , ('c', 'z')
;

select * from test t
WHERE NOT ARRAY['x']::varchar[] <@ (select array_agg(s.v) from stuff s WHERE s.
test_id= t.id)
;

select * from test t
WHERE NOT ARRAY['x', 'y']::varchar[] <@ (select array_agg(s.v) from stuff s 
WHERE s.test_id = t.id)
;

select * from test t
WHERE NOT ARRAY['x', 'y', 'z']::varchar[] <@ (select array_agg(s.v) from stuff 
s WHERE s.test_id = t.id)
;

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)
;

-- 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?






--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com;
 <https://www.visena.com;

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: How to eliminate extra "NOT EXISTS"-query here?
  2023-11-25 13:26 How to eliminate extra "NOT EXISTS"-query here? Andreas Joseph Krogh <andreas@visena.com>
@ 2023-11-25 16:08 ` Tom Lane <tgl@sss.pgh.pa.us>
  2023-11-25 16:52   ` Re: How to eliminate extra "NOT EXISTS"-query here? hector vass <hector.vass@gmail.com>
  2023-11-25 17:18   ` Re: How to eliminate extra "NOT EXISTS"-query here? Andreas Joseph Krogh <andreas@visena.com>
  0 siblings, 2 replies; 6+ messages in thread

From: Tom Lane @ 2023-11-25 16:08 UTC (permalink / raw)
  To: Andreas Joseph Krogh <andreas@visena.com>; +Cc: pgsql-general@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: How to eliminate extra "NOT EXISTS"-query here?
  2023-11-25 13:26 How to eliminate extra "NOT EXISTS"-query here? Andreas Joseph Krogh <andreas@visena.com>
  2023-11-25 16:08 ` Re: How to eliminate extra "NOT EXISTS"-query here? Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-11-25 16:52   ` hector vass <hector.vass@gmail.com>
  2023-11-27 09:29     ` Re: How to eliminate extra "NOT EXISTS"-query here? Dominique Devienne <ddevienne@gmail.com>
  1 sibling, 1 reply; 6+ messages in thread

From: hector vass @ 2023-11-25 16:52 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Andreas Joseph Krogh <andreas@visena.com>; pgsql-general@lists.postgresql.org

Not sure you need to use array why not simple table joins, so a table with
your criteria x y z t joined to stuff to give you candidates that do match,
then left join with coalesce to add the 'd'

select

--a.id,b.test_id,

coalesce(a.id,b.test_id) as finalresult

from test a

left join (

select

test_id

from stuff a

inner join (values ('x'),('y'),('z'),('t')) b (v) using(v)

group by 1

)b on(a.id=b.test_id);


Regards
Hector Vass



On Sat, Nov 25, 2023 at 4:08 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> 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
>
>
>

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: How to eliminate extra "NOT EXISTS"-query here?
  2023-11-25 13:26 How to eliminate extra "NOT EXISTS"-query here? Andreas Joseph Krogh <andreas@visena.com>
  2023-11-25 16:08 ` Re: How to eliminate extra "NOT EXISTS"-query here? Tom Lane <tgl@sss.pgh.pa.us>
  2023-11-25 16:52   ` Re: How to eliminate extra "NOT EXISTS"-query here? hector vass <hector.vass@gmail.com>
@ 2023-11-27 09:29     ` Dominique Devienne <ddevienne@gmail.com>
  2023-11-28 14:14       ` Re: How to eliminate extra "NOT EXISTS"-query here? hector vass <hector.vass@gmail.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Dominique Devienne @ 2023-11-27 09:29 UTC (permalink / raw)
  To: hector vass <hector.vass@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Andreas Joseph Krogh <andreas@visena.com>; pgsql-general@lists.postgresql.org

On Sat, Nov 25, 2023 at 5:53 PM hector vass <hector.vass@gmail.com> wrote:

> Not sure you need to use array why not simple table joins, so a table with
> your criteria x y z t joined to stuff to give you candidates that do match,
> then left join with coalesce to add the 'd'
>
> select
>
> --a.id,b.test_id,
>
> coalesce(a.id,b.test_id) as finalresult
>
> from test a
>
> left join (
>
> select
>
> test_id
>
> from stuff a
>
> inner join (values ('x'),('y'),('z'),('t')) b (v) using(v)
>
> group by 1
>
> )b on(a.id=b.test_id);
>

Hi Hector. Hopefully this is not a stupid question...

How is that equivalent from the `NOT ARRAY ... <@ ...` though?
The inner-join-distinct above will return test_id's on any match, but you
can't know if all array values are matches. Which is different from

> Is the first array contained by the second

from the <@ operator, no?
I'm unfamiliar with these operators, so am I missing something?
Just trying to understand the logic here. Thanks, --DD

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: How to eliminate extra "NOT EXISTS"-query here?
  2023-11-25 13:26 How to eliminate extra "NOT EXISTS"-query here? Andreas Joseph Krogh <andreas@visena.com>
  2023-11-25 16:08 ` Re: How to eliminate extra "NOT EXISTS"-query here? Tom Lane <tgl@sss.pgh.pa.us>
  2023-11-25 16:52   ` Re: How to eliminate extra "NOT EXISTS"-query here? hector vass <hector.vass@gmail.com>
  2023-11-27 09:29     ` Re: How to eliminate extra "NOT EXISTS"-query here? Dominique Devienne <ddevienne@gmail.com>
@ 2023-11-28 14:14       ` hector vass <hector.vass@gmail.com>
  0 siblings, 0 replies; 6+ messages in thread

From: hector vass @ 2023-11-28 14:14 UTC (permalink / raw)
  To: Dominique Devienne <ddevienne@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Andreas Joseph Krogh <andreas@visena.com>; pgsql-general@lists.postgresql.org

Not equivalent to the use of NOT ARRAY and entirely possible I have
misunderstood the requirement ...do you have some more test cases the non
array solution does not work for

Regards
Hector Vass
07773 352559


On Mon, Nov 27, 2023 at 9:29 AM Dominique Devienne <ddevienne@gmail.com>
wrote:

> On Sat, Nov 25, 2023 at 5:53 PM hector vass <hector.vass@gmail.com> wrote:
>
>> Not sure you need to use array why not simple table joins, so a table
>> with your criteria x y z t joined to stuff to give you candidates that do
>> match, then left join with coalesce to add the 'd'
>>
>> select
>>
>> --a.id,b.test_id,
>>
>> coalesce(a.id,b.test_id) as finalresult
>>
>> from test a
>>
>> left join (
>>
>> select
>>
>> test_id
>>
>> from stuff a
>>
>> inner join (values ('x'),('y'),('z'),('t')) b (v) using(v)
>>
>> group by 1
>>
>> )b on(a.id=b.test_id);
>>
>
> Hi Hector. Hopefully this is not a stupid question...
>
> How is that equivalent from the `NOT ARRAY ... <@ ...` though?
> The inner-join-distinct above will return test_id's on any match, but you
> can't know if all array values are matches. Which is different from
>
> > Is the first array contained by the second
>
> from the <@ operator, no?
> I'm unfamiliar with these operators, so am I missing something?
> Just trying to understand the logic here. Thanks, --DD
>

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: How to eliminate extra "NOT EXISTS"-query here?
  2023-11-25 13:26 How to eliminate extra "NOT EXISTS"-query here? Andreas Joseph Krogh <andreas@visena.com>
  2023-11-25 16:08 ` Re: How to eliminate extra "NOT EXISTS"-query here? Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-11-25 17:18   ` Andreas Joseph Krogh <andreas@visena.com>
  1 sibling, 0 replies; 6+ messages in thread

From: Andreas Joseph Krogh @ 2023-11-25 17:18 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@lists.postgresql.org



På lørdag 25. november 2023 kl. 17:08:28, skrev Tom Lane <tgl@sss.pgh.pa.us 
<mailto:tgl@sss.pgh.pa.us>>:
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
Excellent, thanks!






--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com;
 <https://www.visena.com;

^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2023-11-28 14:14 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-11-25 13:26 How to eliminate extra "NOT EXISTS"-query here? Andreas Joseph Krogh <andreas@visena.com>
2023-11-25 16:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-11-25 16:52   ` hector vass <hector.vass@gmail.com>
2023-11-27 09:29     ` Dominique Devienne <ddevienne@gmail.com>
2023-11-28 14:14       ` hector vass <hector.vass@gmail.com>
2023-11-25 17:18   ` Andreas Joseph Krogh <andreas@visena.com>

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