Re: Matching several rows

On Jan 18, 2006, at 20:55 , Volkan YAZICI wrote:

> On Jan 18 05:43, Ivan Steganov wrote:
>> ID RIGHT
>> ---------------------
>> 20 1
>> 20 2
>> 20 5
>> 20 10
>> 30 2
>> 30 10
>>
>> Now I need to find out which IDs have, say rights 2 AND 5 AND 10.
>
> SELECT t.id
> FROM (SELECT id, sum(1) AS s
> FROM id_n_rights
> WHERE rights = ANY(ARRAY[2,5,10])
> GROUP BY id) AS t
> WHERE t.s = 3; -- 3 = length(ARRAY[2,5,10])

Or how about just

select id
from urights
where uright in (2,5,10)
group by id
having count(id) = 3; -- number of urights in IN clause

Find below the query plans for the variations we've seen (as well as
DDL statements):

Michael Glaesemann
grzm myrealbox com

create table urights
(
id integer not null
, uright integer not null
);

copy urights (id, uright) from stdin;
20 1
20 2
20 5
20 10
30 2
30 10
\.

select id
from urights as right_2
join urights as right_5 using (id)
join urights as right_10 using (id)
where right_2.uright = 2
and right_5.uright = 5
and right_10.uright = 10;
QUERY PLAN
------------------------------------------------------------------------
---------------------------------------------
Nested Loop (cost=0.00..3.27 rows=1 width=4) (actual
time=0.053..0.069 rows=1 loops=1)
Join Filter: ("outer".id = "inner".id)
-> Nested Loop (cost=0.00..2.17 rows=1 width=8) (actual
time=0.041..0.052 rows=1 loops=1)
Join Filter: ("inner".id = "outer".id)
-> Seq Scan on urights right_5 (cost=0.00..1.07 rows=1
width=4) (actual time=0.023..0.027 rows=1 loops=1)
Filter: (uright = 5)
-> Seq Scan on urights right_2 (cost=0.00..1.07 rows=2
width=4) (actual time=0.005..0.011 rows=2 loops=1)
Filter: (uright = 2)
-> Seq Scan on urights right_10 (cost=0.00..1.07 rows=2
width=4) (actual time=0.007..0.011 rows=2 loops=1)
Filter: (uright = 10)
Total runtime: 0.337 ms
(11 rows)

select id
from (
select id
from urights
where uright = 2
) as right_2
join (
select id
from urights
where uright = 5
) as right_5 using (id)
join (
select id
from urights
where uright = 10
) as right_10 using (id);
QUERY PLAN
------------------------------------------------------------------------
-------------------------------------
Nested Loop (cost=0.00..3.27 rows=1 width=4) (actual
time=0.062..0.079 rows=1 loops=1)
Join Filter: ("outer".id = "inner".id)
-> Nested Loop (cost=0.00..2.17 rows=1 width=8) (actual
time=0.048..0.060 rows=1 loops=1)
Join Filter: ("inner".id = "outer".id)
-> Seq Scan on urights (cost=0.00..1.07 rows=1 width=4)
(actual time=0.031..0.036 rows=1 loops=1)
Filter: (uright = 5)
-> Seq Scan on urights (cost=0.00..1.07 rows=2 width=4)
(actual time=0.005..0.011 rows=2 loops=1)
Filter: (uright = 2)
-> Seq Scan on urights (cost=0.00..1.07 rows=2 width=4) (actual
time=0.006..0.011 rows=2 loops=1)
Filter: (uright = 10)
Total runtime: 0.299 ms
(11 rows)

SELECT t.id
FROM (SELECT id, sum(1) AS s
FROM urights
WHERE uright = ANY(ARRAY[2,5,10])
GROUP BY id) AS t
WHERE t.s = 3; -- 3 = length(ARRAY[2,5,10])
QUERY PLAN
------------------------------------------------------------------------
-------------------------------------
Subquery Scan t (cost=1.23..1.26 rows=1 width=4) (actual
time=0.102..0.106 rows=1 loops=1)
-> HashAggregate (cost=1.23..1.25 rows=1 width=4) (actual
time=0.095..0.098 rows=1 loops=1)
Filter: (sum(1) = 3)
-> Seq Scan on urights (cost=0.00..1.21 rows=3 width=4)
(actual time=0.038..0.052 rows=5 loops=1)
Filter: (uright = ANY ('{2,5,10}'::integer[]))
Total runtime: 2.521 ms
(6 rows)

select id
from urights
where uright in (2,5,10)
group by id
having count(id) = 3;
QUERY PLAN
------------------------------------------------------------------------
-------------------------------
HashAggregate (cost=1.12..1.15 rows=2 width=4) (actual
time=2.582..2.584 rows=1 loops=1)
Filter: (count(id) = 3)
-> Seq Scan on urights (cost=0.00..1.10 rows=4 width=4) (actual
time=0.042..2.502 rows=5 loops=1)
Filter: ((uright = 2) OR (uright = 5) OR (uright = 10))
Total runtime: 2.944 ms
(5 rows)