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