Is it possible in Phoenix to create a functional index on an UDF that uses as arguments columns from two different tables?

[#1]

We need to execute geo-spatial queries where we use geo-spatial functions that operate on columns from two different tables (YELLOW_TAXI_TRIPS, TAXI_ZONES). The UDF works as a comparison to join the two tables, below is the query in which we want to improve performance applying a functional index.

SELECT ZONES.zone, count(1) AS hits
FROM YELLOW_TAXI_TRIPS AS TRIPS, TAXI_ZONES AS ZONES
WHERE STWithinFromText('POINT('||TRIPS.PICKUP_LONGITUDE||' '||TRIPS.PICKUP_LATITUDE||')', ZONES.geom)
GROUP BY ZONES.zone;

[#2 · Accepted solution]

No, it's not possible. Index may have only one parent table. You need to keep in mind that indexes are updated when you update the parent table. Let's imagine that you have more than one parent table. Any update only one of them would mean that you have to make a lookup for all corresponding records in other parents to update index data. That would be too expensive for simple operation as upsert is.

[#3]

Thanks for your reply!

The question was created twice without knowing it. Sorry!

https://community.hortonworks.com/questions/149484/is-it-possible-in-phoenix-to-create-a-functional-...