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 work_mem = 8MB maintenance_work_mem = 16MB wal_buffers = 16MB 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? Thanks /Niklas |
