Re: Matching several rows

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