pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Andres Sommerhoff <asommerh@chilesat.net>
To: pgsql-sql@postgresql.org
Subject: R-Tree, GiST or B-Tree? I will need it?
Date: Tue, 10 Sep 2002 23:43:59 -0400
Message-ID: <03b801c25945$7c259b10$809f1dc8@s2> (raw)

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." ...
         

Message-ID: <03b801c25945$7c259b10$809f1dc8@s2>
Permalink:  ../03b801c25945$7c259b10$809f1dc8@s2/
Also on:    postgresql.org/message-id/03b801c25945$7c259b10$809f1dc8@s2

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: asommerh@chilesat.net
  Subject: Re: R-Tree, GiST or B-Tree? I will need it?
  In-Reply-To: <03b801c25945$7c259b10$809f1dc8@s2>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox