Could this scenario not be handled by a step that orders the two tables independently, then for the view interleaves the presorted results? Merging two sorted sets into a single sorted set is usually a trivial task, and it could still take advantage of the existing indexes.
William King Senior Engineer Quentus Technologies, INC 1037 NE 65th St Suite 273 Seattle, WA 98115 Main: (877) 211-9337 Office: (206) 388-4772 Cell: (253) 686-5518 william.k...@quentustech.com On 05/25/2013 05:35 PM, Stefan Keller wrote: > 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 > > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
