Search Postgresql Archives

Question about database configuration

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux