Re: Searching union views not using indices

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>