Re: Performance problem with UNION ALL view and domains

[Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>]

"Jeff Larsen" <jlar310(at)gmail(dot)com> writes:
> On Nov 23, 2007 7:29 AM, Dean Rasheed <dean_rasheed(at)hotmail(dot)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