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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-performance by date

  From Date Subject
Next Message Christopher Kings-Lynne 2004-07-21 01:37:52 Re: Unbearably slow cascading deletes
Previous Message andrew 2004-07-20 20:06:46 Re: Unbearably slow cascading deletes