From: Michal Taborsky <michal.taborsky@mall.cz>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Searching union views not using indices
Date: Fri, 04 Nov 2005 16:55:59 +0100
Message-ID: <436B848F.5030402@mall.cz> (raw)
In-Reply-To: <13815.1131118294@sss.pgh.pa.us>
References: <436B4836.7030300@mall.cz>
<13815.1131118294@sss.pgh.pa.us>
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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: michal.taborsky@mall.cz, tgl@sss.pgh.pa.us
Subject: Re: Searching union views not using indices
In-Reply-To: <436B848F.5030402@mall.cz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox