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

Rick Otten <rottenwindfish 'at' gmail.com> writes:

> 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.

depends^^

db=> select count(*) from table where uid = any( string_to_array('11290331,11290332,11290333,11290431',',')::int[]);
count
-------
4
(1 row)

Time: 0.837 ms
db=> select count(*) from table where uid = any( string_to_array('11290331,11290332,11290333,11290431',',')::int[]);
count
-------
4
(1 row)

Time: 0.854 ms
db=> select count(*) from table where array[uid] <@ string_to_array('11290331,11290332,11290333,11290431',',')::int[];
count
-------
4
(1 row)

Time: 52.335 ms
db=> select count(*) from table where array[uid] <@ string_to_array('11290331,11290332,11290333,11290431',',')::int[];
count
-------
4
(1 row)

Time: 44.176 ms

--
Guillaume Cottenceau