From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jeff Larsen <jlar310@gmail.com>
Cc: Dean Rasheed <dean_rasheed@hotmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance problem with UNION ALL view and domains
Date: Fri, 23 Nov 2007 12:41:58 -0500
Message-ID: <12173.1195839718@sss.pgh.pa.us> (raw)
In-Reply-To: <d1f9b6f00711230829q6bbc89b0gf1ed9465dd7daab@mail.gmail.com>
References: <BAY113-W15FECB293A73B10686F8CEF27A0@phx.gbl>
<d1f9b6f00711230829q6bbc89b0gf1ed9465dd7daab@mail.gmail.com>
"Jeff Larsen" <jlar310@gmail.com> writes:
> On Nov 23, 2007 7:29 AM, Dean Rasheed <dean_rasheed@hotmail.com> wrote:
>> I am having a performance problem trying to query a view which is a
>> UNION ALL of 2 tables. I have narrowed the problem down to my use of
>> DOMAINS in the underlying table.
> In my case, the data types in each segment of the union were not
> originally identical, preventing the planner from efficiently pushing
> the qualifications down to the individual segments prior to the union.
> In your case the use of a DOMAIN type may be one of those 'special
> cases' forcing the planner to perform the union first, then apply the
> conditions.
It looks like the problem is that the UNION is taken as producing plain
text output, as you can see with \d:
regression=# \d foo
Table "public.foo"
Column | Type | Modifiers
--------+----------+-----------
a | foo_text | not null
b | text |
Indexes:
"foo_pkey" PRIMARY KEY, btree (a)
regression=# \d foo_v
View "public.foo_v"
Column | Type | Modifiers
--------+------+-----------
a | text |
b | text |
View definition:
SELECT foo.a, foo.b
FROM foo
UNION ALL
SELECT foo.a, NULL::text AS b
FROM foo;
Tracing through the code, I see that this happens because
select_common_type() smashes all domains to base types before doing
anything else. So even though all the inputs are in fact the same
domain type, you end up with the base type as the UNION result type.
Possibly that could be improved sometime, but we certainly wouldn't try
to change it in an existing release branch...
regards, tom lane
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: tgl@sss.pgh.pa.us, jlar310@gmail.com, dean_rasheed@hotmail.com
Subject: Re: Performance problem with UNION ALL view and domains
In-Reply-To: <12173.1195839718@sss.pgh.pa.us>
* 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