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