Re: Matching several rows - Mailing list pgsql-sql

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.07rows=1 width=4)  
 
(actual time=0.031..0.036 rows=1 loops=1)               Filter: (uright = 5)         ->  Seq Scan on urights
(cost=0.00..1.07rows=2 width=4)  
 
(actual time=0.005..0.011 rows=2 loops=1)               Filter: (uright = 2)   ->  Seq Scan on urights
(cost=0.00..1.07rows=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)