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

Поиск
Список
Период
Сортировка
От Rick Otten
Тема Re: =ANY(ARRAY) vs =ANY(ARRAY(expr)) performance
Дата
Msg-id CAMAYy4L_o_gh1DNg7SwRZa+J5bwaon1vSu1y9j78xGi_FP7ZMQ@mail.gmail.com
обсуждение исходный текст
Ответ на =ANY(ARRAY) vs =ANY(ARRAY(expr)) performance  (Ramdip Gill <ramdip.singhgill@gmail.com>)
Список 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.

В списке pgsql-performance по дате отправления:

Предыдущее
От: Ramdip Gill
Дата:
Сообщение: Re: =ANY(ARRAY) vs =ANY(ARRAY(expr)) performance
Следующее
От: Guillaume Cottenceau
Дата:
Сообщение: Re: =ANY(ARRAY) vs =ANY(ARRAY(expr)) performance