For about 5 years now, I have been using a text search engine that I wrote
and maintain.
In the beginning, I hacked up function mechanisms to return multiple value
sets and columns. Then PostgreSQL aded "setof" and it is was cool. Then it
was able to return a set of rows, which was even better.
Lately, I have been thinking that a cool form of index would be some sort
of "persistent reference" index. Like the old ISAM days of yore, a fixed
number could point you right to the row that you want. I'm not sure if the
"persistent reference" is a specific auto numbering column type or
separate index structure or both.
I asked the question how do you get a record without going through an
index, the answer was CTID, which unfortunately changes when the row is
updated.
Now, what I want to brainstorm is some sort of "persistent reference"
where the value is not algorithmically stored, maybe just an offset into a
table. The number of operations should be about 1 per lookup.
Imagine a dynamically growing array that has one slot per row. Every row
is considered unique. Rows which are updated, their CTID is updated in the
reference. (with vacuum?)
Imagine something like this:
create table foobar(id reference, name varchar, value varchar);
select * from foobar where id = 100;
The reference type has an implicit index that is basically a lookup table.
On unique references where the reference value is fairly arbitrary, this
would be a HUGE gain for direct lookups. There is no need for the NlogN of
a tree.
On the surface level, this would be a huge win for websites that use
semi-fixed tables of data.