Re: =ANY(ARRAY) vs =ANY(ARRAY(expr)) performance

From: Rick Otten <rottenwindfish(at)gmail(dot)com>
To: Ramdip Gill <ramdip(dot)singhgill(at)gmail(dot)com>
Cc: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Re: =ANY(ARRAY) vs =ANY(ARRAY(expr)) performance
Date: 2022-11-14 14:11:18
Message-ID: CAMAYy4L_o_gh1DNg7SwRZa+J5bwaon1vSu1y9j78xGi_FP7ZMQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I was able to reproduce a similar issue with using `= ANY(VALUES)`
> instead of `= ANY(ARRAY)`:
>
> 1. fast query using =ANY(ARRAY): https://explain.depesz.com/s/dwP8
> 2. slow query using =ANY(ARRAY(expr)): https://explain.depesz.com/s/3hGb
> 3. slow query using =ANY(VALUES): https://explain.depesz.com/s/cYrn
>
>
I have found the "ANY" operator to be slow in general. It is almost
always faster to use the "<@" operator:
```
-- more intuitive:
select
count(*)
from
testarray
where
'test' = ANY (myarray)
;

-- faster:
select
count(*)
from
testarray
where
ARRAY['test'::varchar] <@ myarray
;
```
It is just one of those things, like replacing "OR" with "UNION ALL"
whenever possible too, that just make queries faster in PostgreSQL without
a ton of effort or fuss.

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Guillaume Cottenceau 2022-11-14 14:22:27 Re: =ANY(ARRAY) vs =ANY(ARRAY(expr)) performance
Previous Message Ramdip Gill 2022-11-14 04:17:17 Re: =ANY(ARRAY) vs =ANY(ARRAY(expr)) performance