Re: View Index and UNION

Hi Tom

You are right: UNION ALL is correct in terms of contents (tables
contents are disjunct) and of performance (no separate sort required
theoretically).
In my specific case even with UNION ALL the planner still chose a "Seq Scan".
Note that there is a KNN index with "ORDER BY ... <-> ..." involved.
I have to dig into my tests in order to give you the EXPLAIN ANALYZE.

Yours, Stefan


2013/5/26 Tom Lane <tgl@sss.pgh.pa.us>:
> Stefan Keller <sfkeller@gmail.com> writes:
>> 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
>
> I think this would work out-of-the-box in 9.1 or later, if you
> made the view use UNION ALL instead of UNION.
>
>                         regards, tom lane