Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id C47371336FA7 for ; Mon, 21 Mar 2011 03:17:12 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 46165-04 for ; Mon, 21 Mar 2011 06:17:02 +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 83D9E133655C for ; Mon, 21 Mar 2011 03:17:01 -0300 (ADT) Received: by qwk3 with SMTP id 3so3932575qwk.19 for ; Sun, 20 Mar 2011 23:17:01 -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=gcBba1AxPm5cB2v4MQ55Vag8SNQPnCZzDJ87rvXUMOQ=; b=ePiyiW7NCmooye0JxWiTWWNCiwooeIICTDMoMHgE/+zixpgPTFnd8Y9EaWFvV6iMJa Y9ceErfK+W0iG+UT2fcIDvc5ciWU6XyO9poO30LfoR4z9Qo7KyIt+1sPiuOyDGfrunJD Nzvfgq+vkdXt5iRTXVFM6B7BhpQtL5/XoYSWQ= 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=amQA9cNkSmys145Hm5DOtdCsKvxd2z7+t6m/T1PYZA9AoH2PNVIHqnF4Sr9yKGbs5p yOfy1drdJhDLqP0Z6ezRCahG63ZgqwZ1nezvlfEJ+QVyOtY3W+LCTLl1BvKCLvGj6eqs /M8pe8dAgDd0TL4LPaaZLNtD7GhW4HAmNBFCo= Received: by 10.229.68.138 with SMTP id v10mr2833916qci.204.1300688221082; Sun, 20 Mar 2011 23:17:01 -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 l17sm4040265qck.32.2011.03.20.23.16.59 (version=TLSv1/SSLv3 cipher=OTHER); Sun, 20 Mar 2011 23:17:00 -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: Mon, 21 Mar 2011 02:16:56 -0400 Cc: pgsql-performance@postgresql.org Content-Transfer-Encoding: quoted-printable Message-Id: <035B29F0-1441-4FC7-8B24-FAC8DB745C89@gmail.com> 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=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001 X-Spam-Level: X-Archive-Number: 201103/239 X-Sequence-Number: 42918 Pavel, thanks for the help. I increased work_mem from 16MB to 64MB, no difference. The queries are = really just a test case. My actual queries are actual just large number = of primary keys that I am selecting from the db: For example: select * from nodes where node_id in ( 1, 2, 3 ..... ) I found that even for small queries, the following is faster: select * from nodes where node_in =3D any (array[1,2,3 .... ]) Its not really a big deal to me, I was just wondering if others could = reproduce it on other systems/versions and if perhaps this is an issue = that I should point out to postgres-dev. Results below: 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.18..3017.19 rows=3D1 width=3D0) (actual = time=3D1017.051..1017.051 rows=3D1 loops=3D1) -> Nested Loop (cost=3D2887.05..3016.68 rows=3D200 width=3D0) = (actual time=3D157.290..986.329 rows=3D100000 loops=3D1) -> HashAggregate (cost=3D2887.05..2889.05 rows=3D200 width=3D4)= (actual time=3D157.252..241.995 rows=3D100000 loops=3D1) -> Limit (cost=3D0.00..1637.05 rows=3D100000 width=3D4) = (actual time=3D0.009..73.942 rows=3D100000 loops=3D1) -> Seq Scan on nodes (cost=3D0.00..12355.34 = rows=3D754734 width=3D4) (actual time=3D0.008..35.428 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.006 rows=3D1 loops=3D100000) Index Cond: (public.nodes.node_id =3D = public.nodes.node_id) Total runtime: 1017.794 ms (8 rows) 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.60..1718.61 rows=3D1 width=3D0) (actual = time=3D485.554..485.555 rows=3D1 loops=3D1) InitPlan 1 (returns $0) -> Limit (cost=3D0.00..1637.05 rows=3D100000 width=3D4) (actual = time=3D0.011..73.037 rows=3D100000 loops=3D1) -> Seq Scan on nodes (cost=3D0.00..12355.34 rows=3D754734 = width=3D4) (actual time=3D0.010..34.462 rows=3D100000 loops=3D1) -> Bitmap Heap Scan on nodes (cost=3D42.67..81.53 rows=3D10 = width=3D0) (actual time=3D433.003..461.108 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=3D432.810..432.810 rows=3D100000 = loops=3D1) Index Cond: (node_id =3D ANY ($0)) Total runtime: 485.638 ms (9 rows) On Mar 21, 2011, at 1:54 AM, Pavel Stehule wrote: > Hello >=20 > I think so HashAggregate goes out of memory - you can try to increase > a work_mem. >=20 > There are better queries for counting duplicit then cross join >=20 > Regards >=20 > Pavel Stehule >=20 > 2011/3/21 Adam Tistler : >> logicops2=3D# explain analyze select count(*) from nodes where = node_id =3D any( Array(select node_id from nodes limit 100000) ); >> QUERY = PLAN >> = --------------------------------------------------------------------------= --------------------------------------------------------------- >> 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) >>=20 >> Time: 510.009 ms >>=20 >>=20 >> logicops2=3D# explain analyze select count(*) from nodes where = node_id in (select node_id from nodes limit 100000); >> QUERY = PLAN >> = --------------------------------------------------------------------------= -------------------------------------------------------------- >> 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) >>=20 >> Time: 1054.864 ms >>=20 >>=20 >>=20 >> On Mar 20, 2011, at 2:51 AM, Pavel Stehule wrote: >>=20 >>> 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 >>=20 >>=20