From: | William King <william(dot)king(at)quentustech(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: View Index and UNION |
Date: | 2013-05-26 01:27:18 |
Message-ID: | 51A164F6.3090308@quentustech.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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(dot)king(at)quentustech(dot)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
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2013-05-26 02:38:55 | Re: background worker and normal exit |
Previous Message | Stefan Keller | 2013-05-26 00:35:11 | View Index and UNION |