pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMatching several rows
9+ messages / 5 participants
[nested] [flat]
* Matching several rows
@ 2006-01-18 04:43 Ivan Steganov <istegan6@gmail.com>
0 siblings, 3 replies; 9+ messages in thread
From: Ivan Steganov @ 2006-01-18 04:43 UTC (permalink / raw)
To: pgsql-sql
Hi,
This is possibly absolutely trivial but I am lost...
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:
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.
What would be the simplest query?
Thanks!
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Matching several rows
@ 2006-01-18 05:04 Michael Glaesemann <grzm@myrealbox.com>
parent: Ivan Steganov <istegan6@gmail.com>
2 siblings, 0 replies; 9+ messages in thread
From: Michael Glaesemann @ 2006-01-18 05:04 UTC (permalink / raw)
To: Ivan Steganov <istegan6@gmail.com>; +Cc: pgsql-sql
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" = 2
and right_5."RIGHT" = 5
and 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Matching several rows
@ 2006-01-18 11:55 Volkan YAZICI <yazicivo@ttnet.net.tr>
parent: Ivan Steganov <istegan6@gmail.com>
2 siblings, 1 reply; 9+ messages in thread
From: Volkan YAZICI @ 2006-01-18 11:55 UTC (permalink / raw)
To: Ivan Steganov <istegan6@gmail.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Matching several rows
@ 2006-01-18 12:33 Michael Glaesemann <grzm@myrealbox.com>
parent: Volkan YAZICI <yazicivo@ttnet.net.tr>
0 siblings, 1 reply; 9+ messages in thread
From: Michael Glaesemann @ 2006-01-18 12:33 UTC (permalink / raw)
To: Volkan YAZICI <yazicivo@ttnet.net.tr>; +Cc: Ivan Steganov <istegan6@gmail.com>; 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.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)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Matching several rows
@ 2006-01-18 12:48 Volkan YAZICI <yazicivo@ttnet.net.tr>
parent: Michael Glaesemann <grzm@myrealbox.com>
0 siblings, 1 reply; 9+ messages in thread
From: Volkan YAZICI @ 2006-01-18 12:48 UTC (permalink / raw)
To: Michael Glaesemann <grzm@myrealbox.com>; +Cc: Ivan Steganov <istegan6@gmail.com>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Matching several rows
@ 2006-01-18 12:59 Michael Glaesemann <grzm@myrealbox.com>
parent: Volkan YAZICI <yazicivo@ttnet.net.tr>
0 siblings, 1 reply; 9+ messages in thread
From: Michael Glaesemann @ 2006-01-18 12:59 UTC (permalink / raw)
To: Volkan YAZICI <yazicivo@ttnet.net.tr>; +Cc: Ivan Steganov <istegan6@gmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Matching several rows
@ 2006-01-18 14:22 Ivan Steganov <istegan6@gmail.com>
parent: Ivan Steganov <istegan6@gmail.com>
2 siblings, 1 reply; 9+ messages in thread
From: Ivan Steganov @ 2006-01-18 14:22 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Matching several rows
@ 2006-01-18 14:28 Patrick JACQUOT <patrick.jacquot@anpe.fr>
parent: Michael Glaesemann <grzm@myrealbox.com>
0 siblings, 0 replies; 9+ messages in thread
From: Patrick JACQUOT @ 2006-01-18 14:28 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Matching several rows
@ 2006-01-18 16:35 Vraj Mohan <vrajmohan@comcast.net>
parent: Ivan Steganov <istegan6@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Vraj Mohan @ 2006-01-18 16:35 UTC (permalink / raw)
To: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2006-01-18 16:35 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-18 04:43 Matching several rows Ivan Steganov <istegan6@gmail.com>
2006-01-18 05:04 ` Michael Glaesemann <grzm@myrealbox.com>
2006-01-18 11:55 ` Volkan YAZICI <yazicivo@ttnet.net.tr>
2006-01-18 12:33 ` Michael Glaesemann <grzm@myrealbox.com>
2006-01-18 12:48 ` Volkan YAZICI <yazicivo@ttnet.net.tr>
2006-01-18 12:59 ` Michael Glaesemann <grzm@myrealbox.com>
2006-01-18 14:28 ` Patrick JACQUOT <patrick.jacquot@anpe.fr>
2006-01-18 14:22 ` Ivan Steganov <istegan6@gmail.com>
2006-01-18 16:35 ` Vraj Mohan <vrajmohan@comcast.net>
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