Message-ID: <436B848F.5030402@mall.cz>
Date: Fri, 04 Nov 2005 16:55:59 +0100
From: Michal Taborsky <michal.taborsky@mall.cz>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Searching union views not using indices
References: <436B4836.7030300@mall.cz> <13815.1131118294@sss.pgh.pa.us>
In-Reply-To: <13815.1131118294@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 8bit

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>