From: Michael Glaesemann <grzm@myrealbox.com>
To: Volkan YAZICI <yazicivo@ttnet.net.tr>
Cc: Ivan Steganov <istegan6@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Matching several rows
Date: Wed, 18 Jan 2006 21:33:20 +0900
Message-ID: <AC724692-16FC-4D58-9063-28E8745CE1BA@myrealbox.com> (raw)
In-Reply-To: <20060118115517.GA578@alamut>
References: <3578f52e0601172043s64561353i689350a1c5d4debb@mail.gmail.com>
<20060118115517.GA578@alamut>
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)
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: grzm@myrealbox.com, yazicivo@ttnet.net.tr, istegan6@gmail.com
Subject: Re: Matching several rows
In-Reply-To: <AC724692-16FC-4D58-9063-28E8745CE1BA@myrealbox.com>
* 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