DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=from:content-type:content-transfer-encoding:subject:date:message-id
	:to:mime-version:x-mailer;
	b=tGLVCi4TQtrH50VgqmGJV7YfO1gYioDus+KlhWubrKFYT1a3r7oImGHsxr2ukQiYvb
	vsoF7/nqY8IIEJbEaBAZXXWsV2/SmcBvB+07tzlKLPOGT3RCuj4GuShMlsr/o/WTMRAa
	FmErTPLIrPaML1pgDjzchU+PWSyl//pPvqwj0=
From: Adam Tistler <atistler@gmail.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Subject: Select in subselect vs select = any array
Date: Sun, 20 Mar 2011 02:47:15 -0400
Message-Id: <75F42287-42D3-4FD5-AFFA-64B1CE3C0195@gmail.com>
To: pgsql-performance@postgresql.org
Mime-Version: 1.0 (Apple Message framework v1082)

I have noticed that SELECT ... =3D ANY(ARRAY(...))  is about twice as =
fast as SELECT IN ( ... ).
Can anyone explain a reason for this?  Results are the bottom and are =
reproducible.  I can test with other versions if that is necessary.

./configure --prefix=3D/usr/local/pgsql84 --with-openssl --with-perl
CentOS release 5.4 (Final)
psql (PostgreSQL) 8.4.1

prompt2=3D# select count(*) from nodes;
 count =20
--------
 754734
(1 row)


prompt2=3D# \d nodes
                                        Table "public.nodes"
    Column    |           Type           |                         =
Modifiers                        =20
=
--------------+--------------------------+--------------------------------=
---------------------------
 node_id      | integer                  | not null default =
nextval(('node_id_seq'::text)::regclass)
 node_type_id | integer                  | not null
 template_id  | integer                  | not null
 timestamp    | timestamp with time zone | default =
('now'::text)::timestamp(6) with time zone
Indexes:
    "nodes_pkey" PRIMARY KEY, btree (node_id)
    "n_node_id_index" btree (node_id)
    "n_node_type_id_index" btree (node_type_id)
    "n_template_id_index" btree (template_id)

prompt2=3D# select count(*) from nodes where node_id =3D any(  =
Array(select node_id from nodes limit 100000) );
 count =20
--------
 100000
(1 row)

Time: 404.530 ms
prompt2=3D# select count(*) from nodes where node_id =3D any(  =
Array(select node_id from nodes limit 100000) );
 count =20
--------
 100000
(1 row)

Time: 407.316 ms
prompt2=3D# select count(*) from nodes where node_id =3D any(  =
Array(select node_id from nodes limit 100000) );
 count =20
--------
 100000
(1 row)

Time: 408.728 ms
prompt2=3D# select count(*) from nodes where node_id in (select node_id =
from nodes limit 100000 );
 count =20
--------
 100000
(1 row)

Time: 793.840 ms
prompt2=3D# select count(*) from nodes where node_id in (select node_id =
from nodes limit 100000 );
 count =20
--------
 100000
(1 row)

Time: 779.137 ms
prompt2=3D# select count(*) from nodes where node_id in (select node_id =
from nodes limit 100000 );
 count =20
--------
 100000
(1 row)

Time: 781.820 ms