Thread: Using oids for fast read-only access?

I've implemented r-trees on top of PostgreSQL (since I need more control over
the r-tree than you can have with the r-tree index that comes with
PostgreSQL) and I would like to know whether there is an efficient way to
dereference the data in the leaves.

Currently the geometry database looks like this:

            Table "geometry"
 Attribute |       Type       | Modifier
-----------+------------------+----------
 id        | integer          |
 x1        | double precision |
 y1        | double precision |
 z1        | double precision |
 x2        | double precision |
 y2        | double precision |
 z2        | double precision |
Indices: geometry_index (on id)

The leaves in the corresponding rtree store id's.
After inserting the geometry, the data is read only, therefore I would like
to replace the id's in the rtree with oids (or anything that has a constant
time access).

The problem is that a "select * from geometry where oid=xxx" is a lot slower
than selecting objects by their id (since the former does a seq scan and the
later an index scan).

The PostgreSQL documentation doesn't say much about oids and their use,
therefore I need al little help...

best wishes,

Christopher Zach

Christopher Zach writes:

> After inserting the geometry, the data is read only, therefore I would like
> to replace the id's in the rtree with oids (or anything that has a constant
> time access).

And what makes you think oids have a more "constant" access time than
integers?  The only thing you will save if you omit the id column is
space.

> The problem is that a "select * from geometry where oid=xxx" is a lot slower
> than selecting objects by their id (since the former does a seq scan and the
> later an index scan).

No surprise if you haven't got an index on oid.

> The PostgreSQL documentation doesn't say much about oids and their use,
> therefore I need al little help...

Oids aren't really any different than other (integer) types, only that
they are generated automatically.

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

Am Freitag, 10. August 2001 12:24 schrieben Sie:
> Christopher Zach writes:
> > After inserting the geometry, the data is read only, therefore I would
> > like to replace the id's in the rtree with oids (or anything that has a
> > constant time access).
>
> And what makes you think oids have a more "constant" access time than
> integers?  The only thing you will save if you omit the id column is
> space.
Because I thought the oid has some direct relationship to the physical page
the row is stored in. The intention was to jump directly to the row instead
of going through a btree...

> Oids aren't really any different than other (integer) types, only that
> they are generated automatically.
It seems so :( But how would you speed up many individual selects on
nonconsecutive rows?

bye,

Christopher Zach