Select in subselect vs select = any array
От | Adam Tistler |
---|---|
Тема | Select in subselect vs select = any array |
Дата | |
Msg-id | 75F42287-42D3-4FD5-AFFA-64B1CE3C0195@gmail.com обсуждение исходный текст |
Ответы |
Re: Select in subselect vs select = any array
|
Список | pgsql-performance |
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 thatis 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
В списке pgsql-performance по дате отправления: