pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed 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
Date: Tue, 3 Jul 2007 13:32:48 -0400
Message-ID: <20070703133248.c6733d2f.wmoran@collaborativefusion.com> (raw )
In-Reply-To: <11416966.post@talk.nabble.com >
References: <11416966.post@talk.nabble.com >
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
view thread (11+ messages) latest in thread
Message-ID: <20070703133248.c6733d2f.wmoran@collaborativefusion.com>
Permalink: ../20070703133248.c6733d2f.wmoran@collaborativefusion.com/
Also on: postgresql.org/message-id/20070703133248.c6733d2f.wmoran@collaborativefusion.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: wmoran@collaborativefusion.com, smiley2211@yahoo.com
Subject: Re: Query is taking 5 HOURS to Complete on 8.1 version
In-Reply-To: <20070703133248.c6733d2f.wmoran@collaborativefusion.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox