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
