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.king@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
>
>