Re: View Index and UNION

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
> 
>