Hi
I've encountered a fundamental problem which - to me - can only be
solved with an (future/possible) real index on views in PostgreSQL
(like the exist already in MS SQL Server and Ora):
Given following schema:
1. TABLE a and TABLE b, each with INDEX on attribute geom.
2. A VIEW with union:
CREATE VIEW myview AS SELECT * FROM a UNION SELECT * FROM b;
3. And a simple query with KNN index and a coordinate "mypos" :
SELECT * FROM myview
ORDER BY ST_Geomfromtext(mypos) <-> myview.geom
Now, the problem is, that for the "order by" it is not enough that
each on the two tables calculate the ordering separately: We want a
total ordering over all involved tables!
In fact, the planner realizes that and chooses a seq scan over all
tuples of table a and b - which is slow and suboptimal!
To me, that's a use case where we would wish to have a distinct index on views.
Any opinions on this?
Yours, Stefan
