X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 927E9D1CCC5 for ; Wed, 10 Mar 2004 17:02:43 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 55299-02 for ; Wed, 10 Mar 2004 13:02:32 -0400 (AST) Received: from ideafix.webnow.com.br (ideafix.webnow.com.br [200.155.4.67]) by svr1.postgresql.org (Postfix) with ESMTP id 7CD20D1CADC for ; Wed, 10 Mar 2004 13:02:29 -0400 (AST) Received: by ideafix.webnow.com.br (Postfix, from userid 48) id BE859197644; Wed, 10 Mar 2004 14:02:23 -0300 (BRT) Received: from 148100.cps.virtua.com.br ([200.174.148.100]) (SquirrelMail authenticated user marcus.magalhaes@vlinfo.com.br) by webmail.webnow.com.br with HTTP; Wed, 10 Mar 2004 14:02:23 -0300 (BRT) Message-ID: <63139.200.174.148.100.1078938143.squirrel@webmail.webnow.com.br> Date: Wed, 10 Mar 2004 14:02:23 -0300 (BRT) Subject: Re: optimizing large query with IN (...) From: "Marcus Andree S. Magalhaes" To: In-Reply-To: References: <62371.200.174.148.100.1078889715.squirrel@webmail.webnow.com.br> X-Priority: 3 Importance: Normal Cc: , X-Mailer: SquirrelMail (version 1.2.11) MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/134 X-Sequence-Number: 5990 Hmm... from the 'performance' point of view, since the data comes from a quite complex select statement, Isn't it better/quicker to have this select replaced by a select into and creating a temporary database? > The problem, as I understand it, is that 7.4 introduced massive > improvements in handling moderately large in() clauses, as long as they > can fit in sort_mem, and are provided by a subselect. > > So, creating a temp table with all the values in it and using in() on > the temp table may be a win: > > begin; > create temp table t_ids(id int); > insert into t_ids(id) values (123); <- repeat a few hundred times > select * from maintable where id in (select id from t_ids); > ...