Richard Broersma wrote:
On Mon, Apr 21, 2008 at 11:02 AM, Colin Wetherbee <cww@xxxxxxxxxxxxxxxx> wrote:
Let's say my points table looks like this:
point_id | location
---------+----------
1 | 010100000000... <-- some PostGIS geometry string
2 | 010100000000...
And, my foo table, which contains data pertaining to these connections,
looks like this:
id | point_id_start | point_id_end
---+----------------+--------------
1 | 1 | 2
And, let's say my function is connect(location1, location2).
I would like to be able to retrieve that connection without using the
connect() procedure. How would I be able to take advantage of a functional
index in this context?
I am not sure what kind of constraints you have on your points table
for location. It location is unique, this might be an example where
you can solve your problem if you use a natural foreign key in foo
instead of a surrogate key.
Yes, every location is currently unique, and I can't think of a
situation where I would have useful duplicates.
Then you could just create an index:
CREATE INDEX ON Foo Connect( location1, location2);
My impression of functional indexes is that they're useful mostly in
WHERE queries, like the following.
SELECT foo, bar, baz FROM some_table WHERE lower(foo) = 'qux';
In this case, the index would be created on lower(foo).
How would I get the value of the functional index out of the index in my
case?
Colin
