Thread: Matching several rows

Matching several rows

From
Ivan Steganov
Date:
Hi,<br /><br />This is possibly absolutely trivial but I am lost...<br /><br />A table URIGHTS which stores an ID and
theRIGHTs this ID has. One ID may have many rights and accordingly records in table, sample:<br /><br />ID        RIGHT
<br/>---------------------<br />20        1<br />20        2<br />20        5<br />20        10<br />30        2<br
/>30       10<br /><br />Now I need to find out which IDs have, say rights 2 AND 5 AND 10.<br /><br />What would be the
simplestquery? <br /><br />Thanks!  

Re: Matching several rows

From
Michael Glaesemann
Date:
On Jan 18, 2006, at 13:43 , Ivan Steganov wrote:

> A table URIGHTS which stores an ID and the RIGHTs this ID has. One  
> ID may have many rights and accordingly records in table, sample:

> Now I need to find out which IDs have, say rights 2 AND 5 AND 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."RIGHT" = 2and right_5."RIGHT" = 5and right_10."RIGHT" = 10
 or

select "ID"
from (select "ID"from "URIGHTS"where "RIGHT" = 2) as right_2
join (select "ID"from "URIGHTS"where "RIGHT" = 5) as right_5 using ("ID")
join (select "ID"from "URIGHTS"where "RIGHT" = 10) as right_10 using ("ID")


Simple is in the eye of the beholder. You might want to compare the  
EXPLAIN ANALYZE output to see if there are any significant  
differences between these queries.

Michael Glaesemann
grzm myrealbox com





Re: Matching several rows

From
Volkan YAZICI
Date:
Hi,

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


HTH.
Regards.


Re: Matching several rows

From
Michael Glaesemann
Date:
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)






Re: Matching several rows

From
Volkan YAZICI
Date:
On Jan 18 09:33, Michael Glaesemann wrote:
> On Jan 18, 2006, at 20:55 , Volkan YAZICI wrote:
> >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;

AFAICS, the bottleneck in above query is ANY(ARRAY[]) clause usage.
Instead of that, if you replace «rights = ANY(ARRAY[2,5,10])» with
«rights IN (2,5,10)» it's overhead decreases to 0.200-0.300ms domain.


Regards.


Re: Matching several rows

From
Michael Glaesemann
Date:
On Jan 18, 2006, at 21:48 , Volkan YAZICI wrote:

> AFAICS, the bottleneck in above query is ANY(ARRAY[]) clause usage.
> Instead of that, if you replace «rights = ANY(ARRAY[2,5,10])» with
> «rights IN (2,5,10)» it's overhead decreases to 0.200-0.300ms domain.

explain analyze
SELECT id
FROM (    SELECT id, sum(1) AS s    FROM urights    WHERE uright in (2,5,10)    GROUP BY id) AS t
WHERE s = 3;                                                 QUERY PLAN
------------------------------------------------------------------------
-------------------------------------
Subquery Scan t  (cost=1.14..1.19 rows=2 width=4) (actual
time=0.106..0.108 rows=1 loops=1)   ->  HashAggregate  (cost=1.14..1.17 rows=2 width=4) (actual
time=0.103..0.105 rows=1 loops=1)         Filter: (sum(1) = 3)         ->  Seq Scan on urights  (cost=0.00..1.10 rows=4
width=4)  
(actual time=0.029..0.038 rows=5 loops=1)               Filter: ((uright = 2) OR (uright = 5) OR (uright = 10))
Total runtime: 0.386 ms
(6 rows)


Michael Glaesemann
grzm myrealbox com





Re: Matching several rows

From
Ivan Steganov
Date:
Thank you to everyone for the great help!<br /><br />I will evaluate all methods in our query (It is actually well
complexerthen this sample) and choose the best one.<br /><br />Is there any "scientific" name to this kind of "several
rowsmatch for one result" data selection? <br /><br />Ivan  

Re: Matching several rows

From
Patrick JACQUOT
Date:
Michael Glaesemann wrote:

>
> On Jan 18, 2006, at 21:48 , Volkan YAZICI wrote:
>
>> AFAICS, the bottleneck in above query is ANY(ARRAY[]) clause usage.
>> Instead of that, if you replace «rights = ANY(ARRAY[2,5,10])» with
>> «rights IN (2,5,10)» it's overhead decreases to 0.200-0.300ms domain.
>
>
> explain analyze
> SELECT id
> FROM (
>     SELECT id, sum(1) AS s
>     FROM urights
>     WHERE uright in (2,5,10)
>     GROUP BY id) AS t
> WHERE s = 3;
>                                                  QUERY PLAN
> ------------------------------------------------------------------------ 
> -------------------------------------
> Subquery Scan t  (cost=1.14..1.19 rows=2 width=4) (actual  
> time=0.106..0.108 rows=1 loops=1)
>    ->  HashAggregate  (cost=1.14..1.17 rows=2 width=4) (actual  
> time=0.103..0.105 rows=1 loops=1)
>          Filter: (sum(1) = 3)
>          ->  Seq Scan on urights  (cost=0.00..1.10 rows=4 width=4)  
> (actual time=0.029..0.038 rows=5 loops=1)
>                Filter: ((uright = 2) OR (uright = 5) OR (uright = 10))
> Total runtime: 0.386 ms
> (6 rows)
>
>
> Michael Glaesemann
> grzm myrealbox com
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>
why not use an having clause in the GROUP BY?
HTH


Re: Matching several rows

From
Vraj Mohan
Date:
It is easier to think of this as SET INTERSECTION which leads to:

SELECT id FROM urights WHERE right = 2
INTERSECT
SELECT id FROM urights WHERE right = 5
INTERSECT
SELECT id FROM urights WHERE right = 10

Ivan Steganov wrote:
> Thank you to everyone for the great help!
> 
> I will evaluate all methods in our query (It is actually well complexer then
> this sample) and choose the best one.
> 
> Is there any "scientific" name to this kind of "several rows match for one
> result" data selection?
> 
> Ivan
>