R-Tree, GiST or B-Tree? I will need it?

From: "Andres Sommerhoff" <asommerh(at)chilesat(dot)net>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: R-Tree, GiST or B-Tree? I will need it?
Date: 2002-09-11 03:43:59
Message-ID: 03b801c25945$7c259b10$809f1dc8@s2
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Thanks Markus for your help (in message "How the R-Tree index works?"), but I guess, I should be more specific in my question.
I have the next situation:
1- A table (A) with data, for example:

ID | what | time | howmuch
--------------------------------------------------
1 | Beer | 03-01-2002 | 10
2 | Whiskey | 06-01-2002 | 3
3 | Beer | 09-01-2002 | 15
4 | Galactic Beer | 11-02-2043 | 40

2- A table (T) with time intervals, for example:

Interval | Inittime | finishtime
-------------------------------------------------
50 | 01-01-2002 | 04-01-2002
51 | 05-01-2002 | 10-01-2002
52 | 12-01-2002 | 12-01-2050

I want to now if is better to use R-Tree or Gist, if I want to know: "Howmuch" is in every "Interval", with something like this:

SELECT Interval, sum(howmuch) from A,T where time>=Inittime and time<finishtime group by interval;

I will have:

Interval | howmuch
------------------------------
50 | 10
51 | 18
52 | 40

The problem is I have a lot of rows in A and T. Is better for the database to use R-Tree? or GiST? Should I use 2 index, one for each table (like a simple join situation)? If I use R-Tree for a B-Tree task will I loss performance? And for GiST?

I have another case, more complex (not conceptually complex, complex for the database), but I will post it when this more simple question be answered. Thanks all and specially thanks Markus for his help.

Andrés Sommerhoff

R-Tree:
"a spatial access method which splits space with hierarchically nested boxes. Objects are indexed in each box which intersects them. The tree is height-balanced." ...

Browse pgsql-sql by date

  From Date Subject
Next Message Michael Paesold 2002-09-11 07:28:57 Re: Rules and Triggers
Previous Message Ross J. Reedstrom 2002-09-10 23:28:16 Re: Rules and Triggers