Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id E366E1B31A57 for ; Thu, 24 May 2012 03:25:39 -0300 (ADT) Received: from news.hub.org ([200.46.204.72]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SXRUN-0004QD-PP for pgsql-performance@postgresql.org; Thu, 24 May 2012 06:25:39 +0000 Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.14.5/8.14.5) with ESMTP id q4O6PMkg075183 for ; Thu, 24 May 2012 03:25:23 -0300 (ADT) (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.14.5/8.14.5/Submit) id q4O68Nb7046743 for pgsql-performance@postgresql.org; Thu, 24 May 2012 03:08:23 -0300 (ADT) (envelope-from news) From: geirB X-Newsgroups: pgsql.performance Subject: Re: Any disadvantages of using =ANY(ARRAY()) instead of IN? Date: Wed, 23 May 2012 05:07:04 -0700 (PDT) Organization: http://groups.google.com Lines: 111 Message-ID: References: <20120510082039.GA27567@tornado.leadboat.com> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 X-Complaints-To: groups-abuse@google.com Complaints-To: groups-abuse@google.com Injection-Info: q2g2000vbv.googlegroups.com; posting-host=78.41.136.12; posting-account=WhKzxwoAAABOHzWVfr3LJGrBbNIi7POr User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:12.0) Gecko/20100101 Firefox/12.0,gzip(gfe) X-Received-Bytes: 3953 To: pgsql-performance@postgresql.org X-Pg-Spam-Score: -0.9 (/) X-Archive-Number: 201205/129 X-Sequence-Number: 46953 I've encoutered similar issues myself (with UNION so far), so I tried to build a simple test case, which may or may not cover Clemens's case. Test case 1 and 2 illustrates the issue, and case 3-9 are variations. My observation: Looks like the optimizer cannot be close friends with both UNION and IN/JOIN at the same time. Actually - it looks like the UNION SELECT kids don't wanna share the IN/JOIN toy we gave them, but are happy when they get their own toys to play with ;) DROP TABLE IF EXISTS table1; CREATE TABLE table1 AS SELECT i AS id FROM generate_series(1, 300000) S(i); CREATE INDEX ON table1(id); ANALYZE table1; -- Test 1: Slow. IN() SELECT * FROM ( SELECT * FROM table1 UNION SELECT * FROM table1 ) Q WHERE id IN (SELECT id FROM table1 LIMIT 10); -- Test 2: Fast. ANY(ARRAY()) SELECT * FROM ( SELECT * FROM table1 UNION SELECT * FROM table1 ) Q WHERE id = ANY(ARRAY(SELECT id FROM table1 LIMIT 10)); -- Test 3: Fast. Duplicate IN. Symptom fix? Or would you call it a "better" query in terms of sql? -except for the unnecessary subquery, which I kept for readability. SELECT * FROM ( SELECT * FROM table1 WHERE id IN (SELECT id FROM table1 LIMIT 10) UNION SELECT * FROM table1 WHERE id IN (SELECT id FROM table1 LIMIT 10) ) Q; -- Test 4: Fast. Duplicate JOIN CTE. WITH id_list AS (SELECT id FROM table1 LIMIT 10) SELECT * FROM ( SELECT * FROM table1 JOIN id_list USING(id) UNION SELECT * FROM table1 JOIN id_list USING(id) ) Q; -- Test 5: Slow. IN(CTE) WITH id_list AS (SELECT id FROM table1 LIMIT 10) SELECT * FROM ( SELECT * FROM table1 UNION SELECT * FROM table1 ) Q WHERE id IN (SELECT * FROM id_list); -- Test 6: Slow. IN(explicit id list) SELECT * FROM ( SELECT * FROM table1 UNION SELECT * FROM table1 ) Q WHERE id IN (SELECT UNNEST('{100001,100002,100003,100004,100005,100006,100007,100008,100009,10010}'::BIGINT[] ) AS id); -- Test 7: Slow. IN(UNNEST(ARRAY()) SELECT * FROM ( SELECT * FROM table1 UNION SELECT * FROM table1 ) Q WHERE id IN (SELECT UNNEST(ARRAY(SELECT id FROM table1 LIMIT 10)) AS id); -- Test 8: Slow. JOIN CTE WITH id_list AS (SELECT id FROM table1 LIMIT 10) SELECT * FROM ( SELECT * FROM table1 UNION SELECT * FROM table1 ) Q JOIN id_list USING(id); -- Test 9: Fast. JOIN CTE + UNION ALL/DISTINCT (not quite the same query) WITH id_list AS (SELECT id FROM table1 LIMIT 10) SELECT DISTINCT * FROM ( SELECT * FROM table1 UNION ALL SELECT * FROM table1 ) Q JOIN id_list USING(id); -- Geir Bostad 9.1.3(x64,win)