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