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 60FC211F636B for ; Mon, 10 Apr 2006 01:48:25 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.137 X-Spam-Level: X-Spam-Status: No, score=0.137 required=5 tests=[AWL=0.137] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id cA+T+bYISso6 for ; Mon, 10 Apr 2006 01:48:22 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id 849D611F628C for ; Mon, 10 Apr 2006 01:48:22 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 88B4A30D11; Mon, 10 Apr 2006 06:48:21 +0200 (MET DST) From: "Qingqing Zhou" X-Newsgroups: pgsql.performance Subject: Re: slow "IN" clause Date: Mon, 10 Apr 2006 12:44:46 +0800 Organization: Hub.Org Networking Services Lines: 19 Message-ID: References: <1144640620.925899.274170@z34g2000cwc.googlegroups.com> X-Complaints-To: usenet@news.hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2800.1506 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506 To: pgsql-performance@postgresql.org X-Archive-Number: 200604/187 X-Sequence-Number: 18271 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. Regards, Qingqing