Tom Lane napsal(a):
Michal Taborsky <michal.taborsky@xxxxxxx> 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>
