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
