Thread: Fast reference without an index?

A question to the hackers:

Is there a way, and if I'm being stupid please tell me, to use something
like a row ID to reference a row in a PostgreSQL database? Allowing the
database to find a specific row without using an index?

I mean, an index has to return something like a row ID for the database to
find the row, right? Granted it has to find the version with the right
transaction ID, but still, you get the idea. In the old days of ISAM
stuff, it would just be the offset into the file.

In my text search system, rather than create an additional indexed column
for row ID, wouldn't it  be more efficient if I could use PostgreSQL's
data for this? This would greatly improve the performance of FTSS as it
would eliminate the extra index operation per row returned.

pgsql@mohawksoft.com writes:
> Is there a way, and if I'm being stupid please tell me, to use something
> like a row ID to reference a row in a PostgreSQL database? Allowing the
> database to find a specific row without using an index?

ctid ... which changes on every update ...
        regards, tom lane

> pgsql@mohawksoft.com writes:
>> Is there a way, and if I'm being stupid please tell me, to use something
>> like a row ID to reference a row in a PostgreSQL database? Allowing the
>> database to find a specific row without using an index?
>
> ctid ... which changes on every update ...

Well, how does an index do it? Say this:

select * from mytable where name = 'foo';

The index must return something. Say I have a row that it constantly being
updated, or has an original item inserted. An item which is valid within
my transaction, and an item which has just be inserted but has a
transaction id greater than mine.


Dosn't the index have some base number which points to the first valid
occurance of the row, and then the valid row is found based on the
transaction ID, or has PG changed? Is that the ctid?

On Tue, Feb 08, 2005 at 04:29:34PM -0500, pgsql@mohawksoft.com wrote:
> > pgsql@mohawksoft.com writes:
> >> Is there a way, and if I'm being stupid please tell me, to use something
> >> like a row ID to reference a row in a PostgreSQL database? Allowing the
> >> database to find a specific row without using an index?
> >
> > ctid ... which changes on every update ...
> 
> Well, how does an index do it?

The index _is_ updated on every update.  And on vacuum too, of course;
so you'd also need to hack VACUUM to make sure your reference is
updated.

Maybe you could store your data in a GiST index ...


-- 
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"[PostgreSQL] is a great group; in my opinion it is THE best open source
development communities in existence anywhere."                (Lamar Owen)