From: Patrick JACQUOT <patrick.jacquot@anpe.fr>
To: pgsql-sql@postgresql.org
Subject: Re: Matching several rows
Date: Wed, 18 Jan 2006 15:28:19 +0100
Message-ID: <43CE5083.1090100@anpe.fr> (raw)
In-Reply-To: <A6164E21-A45C-4308-A38A-4CA86CA9A265@myrealbox.com>
References: <3578f52e0601172043s64561353i689350a1c5d4debb@mail.gmail.com>
<20060118115517.GA578@alamut>
<AC724692-16FC-4D58-9063-28E8745CE1BA@myrealbox.com>
<20060118124834.GC578@alamut>
<A6164E21-A45C-4308-A38A-4CA86CA9A265@myrealbox.com>
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
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: patrick.jacquot@anpe.fr
Subject: Re: Matching several rows
In-Reply-To: <43CE5083.1090100@anpe.fr>
* 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