Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 6A472476552 for ; Tue, 10 Sep 2002 23:46:15 -0400 (EDT) Received: from mail.chilesat.net (mail2.chilesat.net [200.31.43.52]) by postgresql.org (Postfix) with ESMTP id 4A55F476526 for ; Tue, 10 Sep 2002 23:46:14 -0400 (EDT) Received: from s2 (200.31.59.139) by mail.chilesat.net (5.1.046) id 3D704DA3000999CC for pgsql-sql@postgresql.org; Tue, 10 Sep 2002 23:48:02 -0400 Message-ID: <03b801c25945$7c259b10$809f1dc8@s2> From: "Andres Sommerhoff" To: Subject: R-Tree, GiST or B-Tree? I will need it? Date: Tue, 10 Sep 2002 23:43:59 -0400 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_03B5_01C25923.EF6B76C0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200209/78 X-Sequence-Number: 9355 This is a multi-part message in MIME format. ------=_NextPart_000_03B5_01C25923.EF6B76C0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Thanks Markus for your help (in message "How the R-Tree index works?"), but= I guess, I should be more specific in my question.=20 I have the next situation: 1- A table (A) with data, for example: ID | what | time | howmuch -------------------------------------------------- 1 | Beer | 03-01-2002 | 10=20=20=20 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: "Howmu= ch" is in every "Interval", with something like this: SELECT Interval, sum(howmuch) from A,T where time>=3DInittime and time<= finishtime group by interval; I will have: Interval | howmuch ------------------------------=20=20=20=20=20=20=20=20 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 si= mple join situation)? If I use R-Tree for a B-Tree task will I loss perform= ance? And for GiST? I have another case, more complex (not conceptually complex, complex for th= e database), but I will post it when this more simple question be answered.= Thanks all and specially thanks Markus for his help. Andr=E9s Sommerhoff R-Tree: "a spatial access method which splits space with hierarchically nested boxe= s. Objects are indexed in each box which intersects them. The tree is heigh= t-balanced." ... =20=20=20=20=20=20=20=20=20 ------=_NextPart_000_03B5_01C25923.EF6B76C0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Thanks Markus for your help (in message "H= ow the=20 R-Tree index works?"), but I guess, I should be more specific in my questio= n.=20

I have the next situation:

1- A table (A) with data, for= =20 example:

ID = =20 | what | =20 time |=20 howmuch

=20 --------------------------------------------------

=20 1 |=20 Beer | 03-01-2002 | = =20 10

2 &nbs= p;=20 | Whiskey | 06-01-2002 | 3

3 &nbs= p; |=20 Beer | 09-01-2002 |&nb= sp;=20 15

4 &nbs= p; |=20 Galactic Beer | 11-02-2043 | 40

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

Interval&nbs= p;=20 | Inittime | finishtime

=20 -------------------------------------------------

50&nbs= p; =20 | 01-01-2002 | 04-01-2002

51&nbs= p; =20 | 05-01-2002 | 10-01-2002

=20 52 | 12-01-2002 | 12-01-2050

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

SELECT Interval, sum(ho= wmuch)=20 from A,T where time>=3DInittime and time<finishtime group by=20 interval;

I will have:

Interval | howmuch

------------------------------ = =20

50 &nb= sp; =20 | 10

51 &nb= sp; =20 | 18

52 &nb= sp; =20 | 40

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

I have another case, more complex (not con= ceptually=20 complex, complex for the database), but I will post it when this more simpl= e=20 question be answered. Thanks all and specially thanks Markus for his=20 help.

= =20 Andr=E9s Sommerhoff

R-Tree:

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

= ;=20

------=_NextPart_000_03B5_01C25923.EF6B76C0--