Date: Tue, 3 Jul 2007 13:32:48 -0400
From: Bill Moran <wmoran@collaborativefusion.com>
To: smiley2211 <smiley2211@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query is taking 5 HOURS to Complete on 8.1 version
Message-Id: <20070703133248.c6733d2f.wmoran@collaborativefusion.com>
In-Reply-To: <11416966.post@talk.nabble.com>
References: <11416966.post@talk.nabble.com>
Organization: Collaborative Fusion
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

In response to smiley2211 <smiley2211@yahoo.com>:
> 
> This query is taking less than 5 minutes on 7.4 but over 5 hours on 8.1...
> 
> PostgreSQL 8.1.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
> 4.1.0 (SUSE Linux)
> Total runtime: 20448310.101 ms = 5.6800862 hour
> (132 rows)

When was the last time you vacuum analyzed the database?

Also, you don't even provide the query.  I can't imagine how you'd expect
anyone to help you.  If vacuum analyze doesn't fix the problem, please
provide the query, explain output of the query, and the schema of any
tables involved, including information on indexes.

> 
> --postgresql.conf:
> 
> shared_buffers = 114688                 # min 16 or max_connections*2, 8KB
> each
> #temp_buffers = 20000                   # min 100, 8KB each
> #max_prepared_transactions = 5          # can be 0 or more
> # note: increasing max_prepared_transactions costs ~600 bytes of shared
> memory
> # per transaction slot, plus lock space (see max_locks_per_transaction).
> work_mem = 10240                                # size in KB
> maintenance_work_mem = 64384            # min 1024, size in KB
> max_stack_depth = 4096                  # min 100, size in KB
> 
> # - Free Space Map -
> 
> max_fsm_pages = 500000          # min max_fsm_relations*16, 6 bytes each
> max_fsm_relations = 1000        # min 100, ~70 bytes each
> 
> # - Kernel Resource Usage -
> 
> #max_files_per_process = 1000           # min 25
> #preload_libraries = ''
> 
> # - Cost-Based Vacuum Delay -
> 
> #vacuum_cost_delay = 0                  # 0-1000 milliseconds
> #vacuum_cost_page_hit = 1               # 0-10000 credits
> #vacuum_cost_page_miss = 10             # 0-10000 credits
> #vacuum_cost_page_dirty = 20            # 0-10000 credits
> #vacuum_cost_limit = 200                # 0-10000 credits
> 
> # - Background writer -
> 
> #bgwriter_delay = 200                   # 10-10000 milliseconds between
> rounds
> #bgwriter_lru_percent = 1.0             # 0-100% of LRU buffers
> scanned/round
> #bgwriter_lru_maxpages = 5              # 0-1000 buffers max written/round
> #bgwriter_all_percent = 0.333           # 0-100% of all buffers
> scanned/round
> #bgwriter_all_maxpages = 5              # 0-1000 buffers max written/round
> 
> 
> #---------------------------------------------------------------------------
> # WRITE AHEAD LOG
> #---------------------------------------------------------------------------
> 
> # - Settings -
> 
> #fsync = on                             # turns forced synchronization on or
> off
> #wal_sync_method = fsync                # the default is the first option
>                                         # supported by the operating system:
>                                         #   open_datasync
>                                         #   fdatasync
>                                         #   fsync
>                                         #   fsync_writethrough
>                                         #   open_sync
> #full_page_writes = on                  # recover from partial page writes
> #wal_buffers = 8                        # min 4, 8KB each
> #commit_delay = 0                       # range 0-100000, in microseconds
> #commit_siblings = 5                    # range 1-1000
> 
> # - Checkpoints -
> 
> checkpoint_segments = 12                # in logfile segments, min 1, 16MB
> each
> #checkpoint_timeout = 300               # range 30-3600, in seconds
> #checkpoint_warning = 30                # in seconds, 0 is off
> 
> # - Archiving -
> 
> #archive_command = ''                   # command to use to archive a
> logfile
>                                         # segment
> 
> 
> #---------------------------------------------------------------------------
> # QUERY TUNING
> #---------------------------------------------------------------------------
> 
> # - Planner Method Configuration -
> 
> enable_bitmapscan = off
> enable_hashagg = on
> enable_hashjoin = on
> enable_indexscan = on
> enable_mergejoin = on
> enable_nestloop = on
> enable_seqscan = off
> enable_sort = on
> enable_tidscan = on
> 
> # - Planner Cost Constants -
> 
> effective_cache_size = 10000            # typically 8KB each
> random_page_cost = 4                    # units are one sequential page
> fetch
>                                         # cost
> #cpu_tuple_cost = 0.01                  # (same)
> #cpu_index_tuple_cost = 0.001           # (same)
> #cpu_operator_cost = 0.0025             # (same)
> #---------------------------------------------------------------------------
> # LOCK MANAGEMENT
> #---------------------------------------------------------------------------
> 
> #deadlock_timeout = 1000                # in milliseconds
> #max_locks_per_transaction = 64         # min 10
> # note: each lock table slot uses ~220 bytes of shared memory, and there are
> # max_locks_per_transaction * (max_connections + max_prepared_transactions)
> # lock table slots.
> 

-- 
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023