From: | Dean Rasheed <dean_rasheed(at)hotmail(dot)com> |
---|---|
To: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Performance problem with UNION ALL view and domains |
Date: | 2007-11-23 13:29:40 |
Message-ID: | BAY113-W15FECB293A73B10686F8CEF27A0@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Larsen | 2007-11-23 16:29:07 | Re: Performance problem with UNION ALL view and domains |
Previous Message | Heikki Linnakangas | 2007-11-22 19:22:41 | Re: tuning for TPC-C benchmark |