RTREE Index on primary key generated by a sequence

From: Jean-Paul ARGUDO <jean-paul(dot)argudo(at)IDEALX(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Cc: jean-paul(dot)argudo(at)idealx(dot)com
Subject: RTREE Index on primary key generated by a sequence
Date: 2002-01-25 08:56:18
Message-ID: 20020125085618.GA29931@pastis
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi all,

Since I was at first Oracle DBA, I've been told many times at
professional trainings that when there is a table wich primary key is
generated by a sequence, it is worth create a RTREE index on it rather
than a BTREE (for index balancing reasons).

But, I wanted to try it with my PostgreSQL 7.1.3 and:

---
contacts=# \d t_contact
Table "t_contact"
Attribute | Type |
Modifier
-------------------+------------------------+--------------------------------------------------
cnt_id | integer | not null default nextval('contact_id_seq'::text)

[snipped all other columns...]

contacts=# drop index t_contacts_pkey;
DROP

contacts=# create unique index t_contacts_pkey on t_contact using rtree
(cnt_id);
ERROR: DefineIndex: unique indices are only available with the btree
access method

contacts=# create index t_contacts_pkey on t_contact using rtree
(cnt_id);
ERROR: DefineIndex: opclass "int4_ops" not supported by access method
"rtree"
---

So I think there are 2 possible reasons:

1) PostgreSQL has strong algorithms to manage indexes greatly, avoiding
this kind of BTREE index being unbalanced time passing ( I mean a kind
of automatic detection of unbalanced BTREE => triggers a DROP and CREATE
index...)

2) It is not yet implemented and you want to do it for the next version
:)

Whih of 1) & 2) is true?

Where to find more information about index management in PostgreSQL?

Thanks a lot.

--
Jean-Paul ARGUDO IDEALX S.A.S
Consultant bases de données 15-17, av. de Ségur
http://IDEALX.com/ F-75007 PARIS

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Devogelaere 2002-01-25 09:20:01 Re: PostgreSQL crashes with Qmail-SQL
Previous Message Lau NH 2002-01-25 08:46:13 Re: Database access right