> 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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
