Performance problem with UNION ALL view and domains

Hi,

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. So in the test-case below, when the
column "a" is of domain type foo_text, the query runs slowly using
the following plan:

Subquery Scan foo_v (cost=0.00..798.00 rows=100 width=64) (actual time=0.049..24.763 rows=2 loops=1)
Filter: (a = (('foo34'::text)::foo_text)::text)
-> Append (cost=0.00..548.00 rows=20000 width=20) (actual time=0.007..20.338 rows=20000 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..274.00 rows=10000 width=20) (actual time=0.006..7.341 rows=10000 loops=1)
-> Seq Scan on foo (cost=0.00..174.00 rows=10000 width=20) (actual time=0.004..2.366 rows=10000 loops=1)
-> Subquery Scan "*SELECT* 2" (cost=0.00..274.00 rows=10000 width=10) (actual time=0.009..6.536 rows=10000 loops=1)
-> Seq Scan on foo (cost=0.00..174.00 rows=10000 width=10) (actual time=0.007..2.746 rows=10000 loops=1)
Total runtime: 24.811 ms

However, when the column type is text, the query runs fast as I
would expect, using the PK index:

Result (cost=0.00..16.55 rows=2 width=64) (actual time=0.015..0.025 rows=2 loops=1)
-> Append (cost=0.00..16.55 rows=2 width=64) (actual time=0.014..0.023 rows=2 loops=1)
-> Index Scan using foo_pkey on foo (cost=0.00..8.27 rows=1 width=20) (actual time=0.014..0.014 rows=1 loops=1)
Index Cond: (a = (('foo34'::text)::foo_text)::text)
-> Index Scan using foo_pkey on foo (cost=0.00..8.27 rows=1 width=10) (actual time=0.007..0.008 rows=1 loops=1)
Index Cond: (a = (('foo34'::text)::foo_text)::text)
Total runtime: 0.065 ms

(PostgreSQL 8.2.5)

Any ideas?

Thanks, Dean

CREATE OR REPLACE FUNCTION setup()
RETURNS void AS
$$
DECLARE
val int;
BEGIN
DROP TABLE IF EXISTS foo CASCADE;
DROP DOMAIN IF EXISTS foo_text;

CREATE DOMAIN foo_text text;-- CONSTRAINT tt_check CHECK (VALUE LIKE 'foo%');

CREATE TABLE foo
(
a foo_text PRIMARY KEY,
b text
);

val := 0;
WHILE val < 10000 LOOP
INSERT INTO foo VALUES('foo'||val, 'bar'||val);
val := val+1;
END LOOP;

CREATE VIEW foo_v AS
(SELECT a,b from foo) UNION ALL (SELECT a,NULL::text AS b FROM foo);
END;
$$ LANGUAGE plpgsql;

SELECT setup();
ANALYZE foo;

EXPLAIN ANALYZE SELECT * FROM foo_v WHERE a='foo34'::foo_text;

_________________________________________________________________
Feel like a local wherever you go.
http://www.backofmyhand.com