[PERFORM] Problems with PostGreSQL and Windows 2003
Hello, I'm having serious peformance problems with PostGreSQL and Windows Server 2003 Enterprise Edition. The PostgreSQL Server don't starts if I set the shared buffers high than 1GB. All my programs can use only 3 GB of RAM and I have 8GB of RAM. When I try to execute a query in a table about 4 milion registers, my application crashes with an error memory message. The configuration: PostGreSQL 8.2.5 O.S: Windows Server 2003 Enterprise Edition Service Pack 2 Computer: dual quad core Intel(R) Xeon(R) CPU E5345 @ 2.33GHz 8GB of RAM Physical Address Extension 3 HDs in RAID-5 My boot.ini: [boot loader] timeout=30 default=multi(0)disk(0)rdisk(0)partition(2)\WINDOWS [operating systems] multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /PAE /NoExecute=OptOut /3GB PostGreSQL.conf: shared_buffers = 1024MB # min 128kB or max_connections*16kB # (change requires restart) temp_buffers = 32MB # min 800kB #max_prepared_transactions = 5 # can be 0 or more # (change requires restart) # Note: increasing max_prepared_transactions costs ~600 bytes of shared memory # per transaction slot, plus lock space (see max_locks_per_transaction). work_mem =512MB # min 64kB maintenance_work_mem = 256MB# min 1MB max_stack_depth = 2MB # min 100kB # - Free Space Map - max_fsm_pages = 409600 # min max_fsm_relations*16, 6 bytes each # (change requires restart) #max_fsm_relations = 1000 # min 100, ~70 bytes each # (change requires restart) #--- # WRITE AHEAD LOG #--- # - Checkpoints - checkpoint_segments = 128 # in logfile segments, min 1, 16MB each checkpoint_timeout = 15min # range 30s-1h checkpoint_warning = 30s# 0 is off #--- # QUERY TUNING #--- effective_cache_size = 5120MB Thanks, Cláudia. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Performance problem with UNION ALL view and domains
On Nov 23, 2007 7:29 AM, Dean Rasheed <[EMAIL PROTECTED]> wrote: > 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: I don't know much about DOMAINS, but I did learn somethings about views, unions and where conditions when I posted a similar performance question. The best answer was, of course, from Tom Lane here: http://archives.postgresql.org/pgsql-performance/2007-11/msg00041.php In my case, the data types in each segment of the union were not originally identical, preventing the planner from efficiently pushing the qualifications down to the individual segments prior to the union. In your case the use of a DOMAIN type may be one of those 'special cases' forcing the planner to perform the union first, then apply the conditions. Jeff ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] 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=2 width=20) (actual
time=0.007..20.338 rows=2 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..274.00 rows=1
width=20) (actual time=0.006..7.341 rows=1 loops=1)
-> Seq Scan on foo (cost=0.00..174.00 rows=1 width=20)
(actual time=0.004..2.366 rows=1 loops=1)
-> Subquery Scan "*SELECT* 2" (cost=0.00..274.00 rows=1
width=10) (actual time=0.009..6.536 rows=1 loops=1)
-> Seq Scan on foo (cost=0.00..174.00 rows=1 width=10)
(actual time=0.007..2.746 rows=1 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 < 1 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
Re: [PERFORM] Performance problem with UNION ALL view and domains
"Jeff Larsen" <[EMAIL PROTECTED]> writes: > On Nov 23, 2007 7:29 AM, Dean Rasheed <[EMAIL PROTECTED]> wrote: >> 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. > In my case, the data types in each segment of the union were not > originally identical, preventing the planner from efficiently pushing > the qualifications down to the individual segments prior to the union. > In your case the use of a DOMAIN type may be one of those 'special > cases' forcing the planner to perform the union first, then apply the > conditions. It looks like the problem is that the UNION is taken as producing plain text output, as you can see with \d: regression=# \d foo Table "public.foo" Column | Type | Modifiers +--+--- a | foo_text | not null b | text | Indexes: "foo_pkey" PRIMARY KEY, btree (a) regression=# \d foo_v View "public.foo_v" Column | Type | Modifiers +--+--- a | text | b | text | View definition: SELECT foo.a, foo.b FROM foo UNION ALL SELECT foo.a, NULL::text AS b FROM foo; Tracing through the code, I see that this happens because select_common_type() smashes all domains to base types before doing anything else. So even though all the inputs are in fact the same domain type, you end up with the base type as the UNION result type. Possibly that could be improved sometime, but we certainly wouldn't try to change it in an existing release branch... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
