Question about database configuration

From: Niklas Langvig <niklas(dot)langvig(at)globesoft(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Question about database configuration
Date: 2011-02-08 08:01:38
Message-ID: F07D92605693874883FC5F6E4FB1A851058EAF9778@Globe-Exch03.globesoft.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello
We have a database running on Windows Server 2008 standard 32bit using Postgres 8.3
If I run a specific query on this database it takes about 4 seconds

If I do explain analyze on the query it takes about 17 seconds and I get this result in the beginning
Unique (cost=43820.39..43822.51 rows=47 width=81) (actual time=15810.309..15920.974 rows=2548 loops=1)
-> Sort (cost=43820.39..43820.51 rows=47 width=81) (actual time=15810.295..15857.082 rows=17930 loops=1)
Sort Key: com.commseqno, ........ ((subplan))
Sort Method: external sort Disk: 2928kB
-> Nested Loop Left Join (cost=404.24..43819.08 rows=47 width=81) (actual time=42.088..15422.206 rows=17930 loops=1)
-> Hash Left Join (cost=404.24..21797.89 rows=7 width=81) (actual time=41.298..784.648 rows=2627 loops=1)
Hash Cond: (com.commseqno = com2.parentseqno)

So ok I guess I have to add some more work_mem to be able to quicksort using RAM to speed this query up a bit?

Now I made a backup of this database and restored it on a Windows Server 2008 standard 64 bit running Postgres 9.0 64bit
On this server the postgres.conf file is pretty much the same as 8.3 except that I have increased the work_mem to 8MB
my settings are
shared_buffers = 512MB
work_mem = 8MB
maintenance_work_mem = 16MB
wal_buffers = 16MB
effective_cache_size = 1500MB

Now if I run the same query on this database it takes 8 seconds (instead of 4 seconds on version 8.3)
And if I do explain analyze on the query it takes about 100 seconds and the output looks completely different
HashAggregate (cost=8427.62..8713.00 rows=1 width=81) (actual time=112332.799..112338.177 rows=2548 loops=1)
-> Nested Loop Left Join (cost=513.32..8427.58 rows=1 width=81) (actual time=24.545..112233.491 rows=17929 loops=1)
Join Filter: (com.commseqno = com2.parentseqno)
-> Nested Loop Left Join (cost=513.32..7994.81 rows=1 width=77) (actual time=16.160..485.733 rows=17908 loops=1)
-> Nested Loop (cost=513.32..7994.09 rows=1 width=77) (actual time=16.144..370.642 rows=2626 loops=1)

What could it be that I have not set up correctly for this to work as on the 8.3 version?
If it mattars I have run all queries using pgAdmin 1.12.1

Thanks
/Niklas

Browse pgsql-general by date

  From Date Subject
Next Message Szymon Guz 2011-02-08 08:30:05 many schemas or many databases
Previous Message John R Pierce 2011-02-08 05:26:07 Re: index for ilike operation