Re: Searching union views not using indices

Michal Taborsky <michal(dot)taborsky(at)mall(dot)cz> writes:
> We are facing a performance problem with views consisting of several
> unioned tables. The simplified schema is as follows:

Perhaps you should show us the real schema, because I cannot duplicate
your complaint on the toy case you show.

regression=# explain SELECT object_id FROM commonview WHERE link_id=1234567;
QUERY PLAN
------------------------------------------------------------------------------------------------
Subquery Scan commonview (cost=41.40..41.66 rows=13 width=8)
-> Unique (cost=41.40..41.53 rows=13 width=16)
-> Sort (cost=41.40..41.43 rows=13 width=16)
Sort Key: object_id, link_id, loc
-> Append (cost=0.00..41.16 rows=13 width=16)
-> Subquery Scan "*SELECT* 1" (cost=0.00..17.12 rows=5 width=16)
-> Index Scan using fooi on foo (cost=0.00..17.07 rows=5 width=16)
Index Cond: (link_id = 1234567)
-> Subquery Scan "*SELECT* 2" (cost=0.00..24.04 rows=8 width=16)
-> Index Scan using bari on bar (cost=0.00..23.96 rows=8 width=16)
Index Cond: (link_id = 1234567)
(11 rows)

(I had to add indexes on link_id to the example, of course.)

As noted by others, you probably want to be using UNION ALL not UNION,
but that's not the crux of the issue.

regards, tom lane