Thread: Index type

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

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