Tom Lane napsal(a):
Michal Taborsky <[EMAIL PROTECTED]> 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>

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
      choose an index scan if your joining column's datatypes do not
      match

Reply via email to