X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 993965E46CD for ; Mon, 16 Aug 2004 20:15:57 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 96563-04 for ; Mon, 16 Aug 2004 23:15:56 +0000 (GMT) Received: from davinci.ethosmedia.com (server226.ethosmedia.com [209.128.84.226]) by svr1.postgresql.org (Postfix) with ESMTP id EBC655E46CC for ; Mon, 16 Aug 2004 20:15:55 -0300 (ADT) Received: from [64.81.245.111] (account josh@agliodbs.com HELO temoku.sf.agliodbs.com) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) with ESMTP id 6097181; Mon, 16 Aug 2004 16:17:15 -0700 From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: Algolist Subject: Re: Index type Date: Mon, 16 Aug 2004 16:16:27 -0700 User-Agent: KMail/1.6.2 Cc: pgsql-performance@postgresql.org References: <20040626222142.5E3D75AF09D@svr4.postgresql.org> <418482301.20040721014611@manual.ru> In-Reply-To: <418482301.20040721014611@manual.ru> MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Message-Id: <200408161616.27768.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/250 X-Sequence-Number: 7886 Ilia, > If I create btree index on all columns (A,B,C..), here is what explain > analyze gives me: > ----------------------------------------------------------------- > Index Scan using all_ind on test2 (cost=0.00..4.51 rows=1 width=24) > (actual ti me=0.000..0.000 rows=5 loops=1) > Index Cond: ((a >= '2004-07-20 23:50:50'::timestamp without time zone) > AND (a <= '2004-07-21 23:50:50'::timestamp without time zone) AND (b >= > '2004-07-20 23 > > :50:50'::timestamp without time zone) AND (b <= '2004-07-21 > : 23:50:50'::timestamp > > without time zone) AND (c >= '2004-07-20 23:50:50'::timestamp without time > zone ) AND (c <= '2004-07-21 23:50:50'::timestamp without time zone)) Looks good to me. It's a fully indexed search, which it should be with BETWEEN. The only thing you need to ask yourself is whether or not you've selected the columns in the most selective order (e.g. most selective column first). -- --Josh Josh Berkus Aglio Database Solutions San Francisco