From: Adam Tistler <atistler@gmail.com>
To: pgsql-performance@postgresql.org
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> (raw)
I have noticed that SELECT ... = 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=/usr/local/pgsql84 --with-openssl --with-perl
CentOS release 5.4 (Final)
psql (PostgreSQL) 8.4.1
prompt2=# select count(*) from nodes;
count
--------
754734
(1 row)
prompt2=# \d nodes
Table "public.nodes"
Column | Type | Modifiers
--------------+--------------------------+-----------------------------------------------------------
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=# select count(*) from nodes where node_id = any( Array(select node_id from nodes limit 100000) );
count
--------
100000
(1 row)
Time: 404.530 ms
prompt2=# select count(*) from nodes where node_id = any( Array(select node_id from nodes limit 100000) );
count
--------
100000
(1 row)
Time: 407.316 ms
prompt2=# select count(*) from nodes where node_id = any( Array(select node_id from nodes limit 100000) );
count
--------
100000
(1 row)
Time: 408.728 ms
prompt2=# select count(*) from nodes where node_id in (select node_id from nodes limit 100000 );
count
--------
100000
(1 row)
Time: 793.840 ms
prompt2=# select count(*) from nodes where node_id in (select node_id from nodes limit 100000 );
count
--------
100000
(1 row)
Time: 779.137 ms
prompt2=# select count(*) from nodes where node_id in (select node_id from nodes limit 100000 );
count
--------
100000
(1 row)
Time: 781.820 ms
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: atistler@gmail.com
Subject: Re: Select in subselect vs select = any array
In-Reply-To: <75F42287-42D3-4FD5-AFFA-64B1CE3C0195@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox