From: | Michal Taborsky <michal(dot)taborsky(at)mall(dot)cz> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Searching union views not using indices |
Date: | 2005-11-04 15:55:59 |
Message-ID: | 436B848F.5030402@mall.cz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Tom Lane napsal(a):
> Michal Taborsky <michal(dot)taborsky(at)mall(dot)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>
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2005-11-04 16:16:45 | Re: insert performance for win32 |
Previous Message | Merlin Moncure | 2005-11-04 15:41:23 | Re: insert performance for win32 |