Skip site navigation (1) Skip section navigation (2)

Index type

From: Ilia Kantor <algolist(at)manual(dot)ru>
To: pgsql-performance(at)postgresql(dot)org
Subject: Index type
Date: 2004-07-20 21:46:11
Message-ID: 418482301.20040721014611@manual.ru (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)manual(dot)ru


Responses

pgsql-performance by date

Next:From: Christopher Kings-LynneDate: 2004-07-21 01:37:52
Subject: Re: Unbearably slow cascading deletes
Previous:From: andrewDate: 2004-07-20 20:06:46
Subject: Re: Unbearably slow cascading deletes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group