X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 72FA111F65E9 for ; Tue, 11 Apr 2006 02:02:37 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0 X-Spam-Level: X-Spam-Status: No, score=0 required=5 tests=[none] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id CdY9jciz0DzM for ; Tue, 11 Apr 2006 02:02:33 -0300 (ADT) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 536BA11F65E7 for ; Tue, 11 Apr 2006 02:02:34 -0300 (ADT) Received: from kobe.cl (unknown [200.55.206.18]) by svr4.postgresql.org (Postfix) with ESMTP id 50E5A5AF1A0 for ; Tue, 11 Apr 2006 05:02:32 +0000 (GMT) Received: from localhost ([127.0.0.1] helo=localhost.localdomain) by kobe.cl with esmtp (Exim 4.43) id 1FTB1J-0005GL-7b; Tue, 11 Apr 2006 01:02:29 -0400 Subject: Re: slow "IN" clause From: Vinko Vrsalovic Reply-To: el@vinko.cl To: Qingqing Zhou Cc: pgsql-performance@postgresql.org In-Reply-To: References: <1144640620.925899.274170@z34g2000cwc.googlegroups.com> Content-Type: text/plain Date: Tue, 11 Apr 2006 01:02:28 -0400 Message-Id: <1144731748.9011.2.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.4.1 Content-Transfer-Encoding: 7bit X-Archive-Number: 200604/225 X-Sequence-Number: 18309 On lun, 2006-04-10 at 12:44 +0800, Qingqing Zhou wrote: > wrote > > I have a slow sql: > > SELECT * FROM mytable WHERE id IN (1,3,5,7,....3k here...); > > mytable is about 10k rows. > > > > if don't use the "IN" clause, it will cost 0,11 second, otherwise it > > will cost 2.x second > > I guess pg use linear search to deal with IN clause, is there any way > > to let pg use other search method with IN clause? (ex.Binary Search or > > hash Search) > > > > If you can put (1, 3, .., 3k) in a table, PG may choose a hash join. And maybe using SELECT * FROM yourtable WHERE id < 6002 AND id % 2 = 1; turns out to be faster, if we are allowed to extrapolate from the example. V.