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 984D79DC984 for ; Wed, 18 Jan 2006 10:42:46 -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 55637-07 for ; Wed, 18 Jan 2006 10:42:50 -0400 (AST) X-Greylist: delayed 00:14:25.644863 by SQLgrey- Received: from smtp3.anpe.fr (unknown [80.124.143.18]) by postgresql.org (Postfix) with ESMTP id 47C0F9DC9B3 for ; Wed, 18 Jan 2006 10:42:44 -0400 (AST) Received: from HOMERE01.anpe.fr (localhost.localdomain [127.0.0.1]) by smtp3.anpe.fr (antivirus) with ESMTP id 57A3473CDB for ; Wed, 18 Jan 2006 15:28:17 +0100 (CET) Received: from [10.0.32.170] ([10.0.32.170]) by HOMERE01.anpe.fr with Microsoft SMTPSVC(6.0.3790.1830); Wed, 18 Jan 2006 15:28:01 +0100 Message-ID: <43CE5083.1090100@anpe.fr> Date: Wed, 18 Jan 2006 15:28:19 +0100 From: Patrick JACQUOT User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: Matching several rows References: <3578f52e0601172043s64561353i689350a1c5d4debb@mail.gmail.com> <20060118115517.GA578@alamut> <20060118124834.GC578@alamut> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-OriginalArrivalTime: 18 Jan 2006 14:28:01.0478 (UTC) FILETIME=[62E9DE60:01C61C3B] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.047 required=5 tests=[AWL=0.047] X-Spam-Score: 0.047 X-Spam-Level: X-Archive-Number: 200601/122 X-Sequence-Number: 23911 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