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
