Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1ouaM9-0005cL-5k
	for pgsql-performance@arkaria.postgresql.org; Mon, 14 Nov 2022 14:22:37 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1ouaM7-0002Hc-SQ
	for pgsql-performance@arkaria.postgresql.org; Mon, 14 Nov 2022 14:22:35 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <gc@mnc.ch>)
	id 1ouaM7-0002HD-J9
	for pgsql-performance@lists.postgresql.org; Mon, 14 Nov 2022 14:22:35 +0000
Received: from mail.mnc.ch ([62.2.77.205])
	by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <gc@mnc.ch>)
	id 1ouaM4-00070i-Am
	for pgsql-performance@lists.postgresql.org; Mon, 14 Nov 2022 14:22:34 +0000
X-Virus-Scanned: amavisd-new at mnc.ch
Sender: gc@mnc.ch
Received: by localhost.localdomain (Postfix, from userid 1000)
	id EA7671C42DA; Mon, 14 Nov 2022 15:22:27 +0100 (CET)
From: Guillaume Cottenceau <gc@mnc.ch>
To: Rick Otten <rottenwindfish@gmail.com>
Cc: Ramdip Gill <ramdip.singhgill@gmail.com>,
  pgsql-performance@lists.postgresql.org
Subject: Re: =ANY(ARRAY) vs =ANY(ARRAY(expr)) performance
References: 
 <CAGg-0s_dqmO3guJu9=Nri1hsiEcdAYasAJLW29_r92LUC3MC5Q@mail.gmail.com>
	<CAMAYy4L_o_gh1DNg7SwRZa+J5bwaon1vSu1y9j78xGi_FP7ZMQ@mail.gmail.com>
Date: Mon, 14 Nov 2022 15:22:27 +0100
In-Reply-To: 
 <CAMAYy4L_o_gh1DNg7SwRZa+J5bwaon1vSu1y9j78xGi_FP7ZMQ@mail.gmail.com>
	(Rick Otten's message of "Mon, 14 Nov 2022 09:11:18 -0500")
Message-ID: <87zgctzkd8.fsf@mnc.ch>
MIME-Version: 1.0
Content-Type: text/plain
List-Id: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
Archived-At: <https://www.postgresql.org/message-id/87zgctzkd8.fsf%40mnc.ch>
Precedence: bulk

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