Received: from localhost (pluto.hub.org [200.46.204.4]) by postgresql.org (Postfix) with ESMTP id 0829B9F9ED0 for ; Thu, 2 Nov 2006 06:53:39 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.4]) (amavisd-new, port 10024) with ESMTP id 59398-09 for ; Thu, 2 Nov 2006 06:53:32 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.188]) by postgresql.org (Postfix) with ESMTP id B97A79F976E for ; Thu, 2 Nov 2006 06:53:35 -0400 (AST) Received: by nf-out-0910.google.com with SMTP id g2so1100684nfe for ; Thu, 02 Nov 2006 02:53:34 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:from:to:references:subject:date:mime-version:content-type:content-transfer-encoding:x-priority:x-msmail-priority:x-mailer:x-mimeole; b=Her7DlCqL8eabefe3UUHH8lgKx7pK6PHhq2i93BdW+VNfmfyY+l6l4o/HlDmWLmr8W4bhN0GmH+MH9xJSEtzdloic+XYynnf73HlBr5KDFr365cL/Jm9wXJYGZacWL7I6F7qw3Yy1omQWXj6l/aMnNmWszODoESvvUa7BLzsQuY= Received: by 10.48.220.15 with SMTP id s15mr5416432nfg.1162464814253; Thu, 02 Nov 2006 02:53:34 -0800 (PST) Received: from maniek ( [81.219.252.2]) by mx.google.com with ESMTP id a23sm8665967nfc.2006.11.02.02.53.33; Thu, 02 Nov 2006 02:53:33 -0800 (PST) Message-ID: <05e401c6fe6d$56d13780$0c67a8c0@maniek> From: "Marcin Mank" To: "John Major" , References: <4547D9CE.2040705@cbio.mskcc.org> Subject: Re: Help w/speeding up range queries? Date: Thu, 2 Nov 2006 11:54:57 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1807 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1807 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/18 X-Sequence-Number: 21788 > Ie: select FeatureID from SIMPLE_TABLE where FeatureChromosomeName like > 'chrX' and StartPosition > 1000500 and EndPosition < 2000000; How about ( this assumes that StartPosition <= EndPosition ): select FeatureID from SIMPLE_TABLE where FeatureChromosomeName llike 'chrX' and StartPosition > 1000500 and StartPosition < 2000000 and EndPosition > 1000500 and EndPosition < 2000000; This at least should help the planner with estimating number of rows. Also think twice when You assume that a query with ILIKE will use an index. Read about varchar_pattern_ops. Make an index on (FeatureChromosomeName,StartPosition) , and all should be fine. Greetings Marcin