Query is taking 5 HOURS to Complete on 8.1 version - Mailing list pgsql-performance

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)

--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.

--
View this message in context:
http://www.nabble.com/Query-is-taking-5-HOURS-to-Complete-on-8.1-version-tf4019778.html#a11416966
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.