Tom Lane napsal(a):
> Michal Taborsky <michal.taborsky@mall.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.
> As noted by others, you probably want to be using UNION ALL not UNION,
> but that's not the crux of the issue.
OK. Mystery (sort of) solved. After you told me it works for you I had
to assume the problem was somewhere else. And, indeed, it was, though
it's not too obvious.
The two attributes are actually not of tybe bigint, but of type
"crm_object_id", which is created as follows:
CREATE DOMAIN "public"."crm_object_id" AS
bigint NULL;
Everything started working perfectly after I modified the view like this:
CREATE VIEW commonview AS
SELECT foo_object_id::bigint as object_id, link_id::bigint, 'It is in
foo' as loc FROM foo
UNION
SELECT bar_object_id::bigint as object_id, link_id::bigint, 'It is in
bar' as loc FROM bar
Not even modifying the select as this did not help:
explain SELECT object_id FROM commonview WHERE
link_id=1234567::crm_object_id;
Is this a bug or feature?
--
Michal Táborský
CTO, Internet Mall, a.s.
Internet Mall - obchody, které si oblíbíte
<http://www.MALL.cz>