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

Rick Otten <rottenwindfish@gmail.com>

From: Rick Otten <rottenwindfish@gmail.com>
To: Ramdip Gill <ramdip.singhgill@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Date: 2022-11-14T14:11:18Z
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.