X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 5160B9DC9B3 for ; Wed, 18 Jan 2006 08:33:27 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 33715-08-2 for ; Wed, 18 Jan 2006 08:33:29 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from smtp-send.myrealbox.com (smtp-send.myrealbox.com [151.155.5.143]) by postgresql.org (Postfix) with ESMTP id 7367F9DC9E1 for ; Wed, 18 Jan 2006 08:33:24 -0400 (AST) Received: from [172.16.1.181] grzm [61.197.227.146] by smtp-send.myrealbox.com with NetMail SMTP Agent $Revision: 1.6 $ on Linux; Wed, 18 Jan 2006 05:33:26 -0700 In-Reply-To: <20060118115517.GA578@alamut> References: <3578f52e0601172043s64561353i689350a1c5d4debb@mail.gmail.com> <20060118115517.GA578@alamut> Mime-Version: 1.0 (Apple Message framework v746.2) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: Cc: Ivan Steganov , pgsql-sql@postgresql.org Content-Transfer-Encoding: 7bit From: Michael Glaesemann Subject: Re: Matching several rows Date: Wed, 18 Jan 2006 21:33:20 +0900 To: Volkan YAZICI X-Mailer: Apple Mail (2.746.2) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.354 required=5 tests=[AWL=0.354] X-Spam-Score: 0.354 X-Spam-Level: X-Archive-Number: 200601/117 X-Sequence-Number: 23906 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)