Colin Wetherbee wrote:
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?
I think I now see where you're going with this, but this makes my
problem somewhat more interesting.
I can imagine two ways of solving this.
First, an INDEX with a JOIN... (I can see Tom Lane laughing at me now.)
CREATE INDEX foo_connect_idx
ON foo connect(p_start.location, p_end.location)
JOIN points AS p_start ON foo.point_id_start = p_start.point_id
JOIN points AS p_end ON foo.point_id_end = p_end.point_id;
Just in case this might work, I checked the documentation and found no
mention of JOIN anywhere in the INDEX sections.
Second, bury the JOIN part in my PL/Perl function and use spi_ functions
to retrieve my actual PostGIS locations so the process is transparent to
CREATE INDEX.
Hmm.
Colin
