Re: View Index and UNION

Yes, it actually does, but the planner chooses a seq scan to prepare for that.

-S.

2013/5/26 William King <william.king@quentustech.com>:
> 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
>>
>>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers