Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id BB4EB1337B54 for ; Mon, 21 Mar 2011 00:21:08 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 87912-09 for ; Mon, 21 Mar 2011 03:21:00 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-qw0-f46.google.com (mail-qw0-f46.google.com [209.85.216.46]) by mail.postgresql.org (Postfix) with ESMTP id 92A3C1337B3E for ; Mon, 21 Mar 2011 00:21:00 -0300 (ADT) Received: by qwk3 with SMTP id 3so3889081qwk.19 for ; Sun, 20 Mar 2011 20:20:59 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:subject:mime-version:content-type:from :in-reply-to:date:cc:content-transfer-encoding:message-id:references :to:x-mailer; bh=tAEy0OqiXSUJkSx5jqdjNNhertXRmwC9APB8nIoPppk=; b=jJWaS/E4EgeYeX1A1UGsgpcaYnXKr263STjw0eVaXFXIqezffxGkFSocHfrSTdXm+T ojRYfp7HTKDyc2orVYN6vbu87tMPndoRyHYM6KmQkOan991u5hBQAkSWbVysbptN7NSb NfTNE+4AcvRDfPJRhQ00SKSUAsE6uoaktp2wY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=subject:mime-version:content-type:from:in-reply-to:date:cc :content-transfer-encoding:message-id:references:to:x-mailer; b=tL8yFwGAIfW4SNi8FfOttR/+4azFwg4LqWU3nkqfYo3h0+qHzAKKUSAMHBxQ73+Aym kjuI2Whrlq58cBeDEa0PrFyDLjzE6dfxx97A4UiqEvko2QdQQjhiAROSY0gVvT1n40BL hcIpxECo68Z/UcJgU2nWtqMIrku4GN1pRrKnQ= Received: by 10.229.8.212 with SMTP id i20mr2885658qci.22.1300677659817; Sun, 20 Mar 2011 20:20:59 -0700 (PDT) Received: from [192.168.1.6] (146-115-76-62.c3-0.nat-ubr5.sbo-nat.ma.cable.rcn.com [146.115.76.62]) by mx.google.com with ESMTPS id c27sm3974288qck.10.2011.03.20.20.20.57 (version=TLSv1/SSLv3 cipher=OTHER); Sun, 20 Mar 2011 20:20:58 -0700 (PDT) Subject: Re: Select in subselect vs select = any array Mime-Version: 1.0 (Apple Message framework v1082) Content-Type: text/plain; charset=us-ascii From: Adam Tistler In-Reply-To: Date: Sun, 20 Mar 2011 23:20:56 -0400 Cc: pgsql-performance@postgresql.org Content-Transfer-Encoding: quoted-printable Message-Id: References: <75F42287-42D3-4FD5-AFFA-64B1CE3C0195@gmail.com> To: Pavel Stehule X-Mailer: Apple Mail (2.1082) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, RCVD_IN_DNSWL_LOW=-0.7 X-Spam-Level: X-Archive-Number: 201103/237 X-Sequence-Number: 42916 logicops2=3D# explain analyze select count(*) from nodes where node_id =3D= any( Array(select node_id from nodes limit 100000) ); QUERY = PLAN =20 = --------------------------------------------------------------------------= --------------------------------------------------------------- Aggregate (cost=3D1718.59..1718.60 rows=3D1 width=3D0) (actual = time=3D509.126..509.127 rows=3D1 loops=3D1) InitPlan 1 (returns $0) -> Limit (cost=3D0.00..1637.04 rows=3D100000 width=3D4) (actual = time=3D0.010..76.604 rows=3D100000 loops=3D1) -> Seq Scan on nodes (cost=3D0.00..12355.41 rows=3D754741 = width=3D4) (actual time=3D0.008..38.105 rows=3D100000 loops=3D1) -> Bitmap Heap Scan on nodes (cost=3D42.67..81.53 rows=3D10 = width=3D0) (actual time=3D447.274..484.283 rows=3D100000 loops=3D1) Recheck Cond: (node_id =3D ANY ($0)) -> Bitmap Index Scan on n_node_id_index (cost=3D0.00..42.67 = rows=3D10 width=3D0) (actual time=3D447.074..447.074 rows=3D100000 = loops=3D1) Index Cond: (node_id =3D ANY ($0)) Total runtime: 509.209 ms (9 rows) Time: 510.009 ms logicops2=3D# explain analyze select count(*) from nodes where node_id = in (select node_id from nodes limit 100000); QUERY = PLAN =20 = --------------------------------------------------------------------------= -------------------------------------------------------------- Aggregate (cost=3D3017.17..3017.18 rows=3D1 width=3D0) (actual = time=3D1052.866..1052.866 rows=3D1 loops=3D1) -> Nested Loop (cost=3D2887.04..3016.67 rows=3D200 width=3D0) = (actual time=3D167.310..1021.540 rows=3D100000 loops=3D1) -> HashAggregate (cost=3D2887.04..2889.04 rows=3D200 width=3D4)= (actual time=3D167.198..251.205 rows=3D100000 loops=3D1) -> Limit (cost=3D0.00..1637.04 rows=3D100000 width=3D4) = (actual time=3D0.008..80.090 rows=3D100000 loops=3D1) -> Seq Scan on nodes (cost=3D0.00..12355.41 = rows=3D754741 width=3D4) (actual time=3D0.007..41.566 rows=3D100000 = loops=3D1) -> Index Scan using n_node_id_index on nodes (cost=3D0.00..0.63= rows=3D1 width=3D4) (actual time=3D0.006..0.007 rows=3D1 loops=3D100000) Index Cond: (public.nodes.node_id =3D = public.nodes.node_id) Total runtime: 1053.523 ms (8 rows) Time: 1054.864 ms On Mar 20, 2011, at 2:51 AM, Pavel Stehule wrote: > Hello >=20 > 2011/3/20 Adam Tistler : >> 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. >>=20 >=20 > send a result of EXPLAIN ANALYZE SELECT ..., please >=20 > The reasons can be different - less seq scans, indexes >=20 > Regards >=20 > Pavel Stehule >=20 >=20 >=20 >> ./configure --prefix=3D/usr/local/pgsql84 --with-openssl --with-perl >> CentOS release 5.4 (Final) >> psql (PostgreSQL) 8.4.1 >>=20 >> prompt2=3D# select count(*) from nodes; >> count >> -------- >> 754734 >> (1 row) >>=20 >>=20 >> prompt2=3D# \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) >>=20 >> prompt2=3D# select count(*) from nodes where node_id =3D any( = Array(select node_id from nodes limit 100000) ); >> count >> -------- >> 100000 >> (1 row) >>=20 >> Time: 404.530 ms >> prompt2=3D# select count(*) from nodes where node_id =3D any( = Array(select node_id from nodes limit 100000) ); >> count >> -------- >> 100000 >> (1 row) >>=20 >> Time: 407.316 ms >> prompt2=3D# select count(*) from nodes where node_id =3D any( = Array(select node_id from nodes limit 100000) ); >> count >> -------- >> 100000 >> (1 row) >>=20 >> Time: 408.728 ms >> prompt2=3D# select count(*) from nodes where node_id in (select = node_id from nodes limit 100000 ); >> count >> -------- >> 100000 >> (1 row) >>=20 >> Time: 793.840 ms >> prompt2=3D# select count(*) from nodes where node_id in (select = node_id from nodes limit 100000 ); >> count >> -------- >> 100000 >> (1 row) >>=20 >> Time: 779.137 ms >> prompt2=3D# select count(*) from nodes where node_id in (select = node_id from nodes limit 100000 ); >> count >> -------- >> 100000 >> (1 row) >>=20 >> Time: 781.820 ms >>=20 >>=20 >> -- >> Sent via pgsql-performance mailing list = (pgsql-performance@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-performance >>=20