Re: Performance problem with UNION ALL view and domains - Mailing list pgsql-performance

From Tom Lane
Subject Re: Performance problem with UNION ALL view and domains
Date
Msg-id 12173.1195839718@sss.pgh.pa.us
Whole thread Raw
In response to Re: Performance problem with UNION ALL view and domains  ("Jeff Larsen" <jlar310@gmail.com>)
Responses Re: Performance problem with UNION ALL view and domains  (Dean Rasheed <dean_rasheed@hotmail.com>)
List pgsql-performance
"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

pgsql-performance by date:

Previous
From: "Jeff Larsen"
Date:
Subject: Re: Performance problem with UNION ALL view and domains
Next
From: claudia.amorim@pcinformatica.com.br
Date:
Subject: Problems with PostGreSQL and Windows 2003