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 A366DD1B2D9 for ; Tue, 20 Jul 2004 18:46:17 -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 46793-08 for ; Tue, 20 Jul 2004 21:46:14 +0000 (GMT) Received: from umail.ru (umail.mtu.ru [195.34.32.101]) by svr1.postgresql.org (Postfix) with ESMTP id 10B46D1B1AE for ; Tue, 20 Jul 2004 18:46:10 -0300 (ADT) Received: from [83.237.28.208] (HELO ppp83-237-28-208.pppoe.mtu-net.ru) by umail.ru (CommuniGate Pro SMTP 4.2b6) with ESMTP id 279168535 for pgsql-performance@postgresql.org; Wed, 21 Jul 2004 01:46:10 +0400 Date: Wed, 21 Jul 2004 01:46:11 +0400 From: Ilia Kantor X-Mailer: The Bat! (v2.04.7) Business Reply-To: Algolist X-Priority: 3 (Normal) Message-ID: <418482301.20040721014611@manual.ru> To: pgsql-performance@postgresql.org Subject: Index type In-Reply-To: <20040626222142.5E3D75AF09D@svr4.postgresql.org> References: <20040626222142.5E3D75AF09D@svr4.postgresql.org> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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: 200407/162 X-Sequence-Number: 7553 Hello, I have a request like SELECT ... WHERE x<=A<=y AND t<=B<=u AND z<=C<=w AND .. 5 columns are in BETWEEN clauses. What is the best index I could use? 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)) Is such search really optimal? I remember we used k-d trees for geometric data with independent coords.. Is that the same as btree for multiple columns I wonder. -- Best regards, Ilia mailto:algolist@manual.ru