Performance problem with UNION ALL view and domains

[Dean Rasheed <dean_rasheed@xxxxxxxxxxx>]

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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match