Skip site navigation (1) Skip section navigation (2)

Re: Performance problem with UNION ALL view and domains

From: Dean Rasheed <dean_rasheed(at)hotmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Jeff Larsen <jlar310(at)gmail(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Performance problem with UNION ALL view and domains
Date: 2007-11-24 12:06:01
Message-ID: BAY113-W96CB827EE7E74D741F949F27B0@phx.gbl (view raw or flat)
Thread:
Lists: pgsql-performance
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq


Thanks for your replies. It looks like I can cure the performance problem by casting to the
base type in the view definition:

CREATE VIEW foo_v AS SELECT a::text,b from foo UNION ALL SELECT a::text,NULL:\
:text AS b FROM foo;

Interestingly though, if I cast back to the domain type after taking the union, then the
view has the correct type, but the performance problem comes back in a different way:

CREATE VIEW foo_v AS SELECT foo_u.a::foo_text, foo_u.b FROM
(SELECT a::text,b from foo UNION ALL SELECT a::text,NULL::text AS b FROM foo) as foo_u;

lookup=> \d foo_v
      View "public.foo_v"
 Column |   Type   | Modifiers
--------+----------+-----------
 a      | foo_text |
 b      | text     |
View definition:
 SELECT foo_u.a::foo_text AS a, foo_u.b
   FROM ( SELECT foo.a::text AS a, foo.b
           FROM foo
UNION ALL
         SELECT foo.a::text AS a, NULL::text AS b
           FROM foo) foo_u;

 Result  (cost=0.00..399.00 rows=100 width=64) (actual time=0.023..6.777 rows=2 loops=1)
   ->  Append  (cost=0.00..399.00 rows=100 width=64) (actual time=0.022..6.775 rows=2 loops=1)
         ->  Seq Scan on foo  (cost=0.00..199.00 rows=50 width=20) (actual time=0.022..3.409 rows=1 loops=1)
               Filter: ((((a)::text)::foo_text)::text = (('foo34'::text)::foo_text)::text)
         ->  Seq Scan on foo  (cost=0.00..199.00 rows=50 width=10) (actual time=0.016..3.364 rows=1 loops=1)
               Filter: ((((a)::text)::foo_text)::text = (('foo34'::text)::foo_text)::text)
 Total runtime: 6.849 ms

So the planner has been able to push the condition down into the bottom tables, but it
can't use the PK index. Is this because of all the casts?

Dean.

_________________________________________________________________
100’s of Music vouchers to be won with MSN Music
https://www.musicmashup.co.uk

In response to

pgsql-performance by date

Next:From: claudia.amorimDate: 2007-11-25 04:57:01
Subject: Re: Problems with PostGreSQL and Windows 2003
Previous:From: claudia.amorimDate: 2007-11-24 12:00:25
Subject: Re: Problems with PostGreSQL and Windows 2003

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group