it seems like the difference is probably related to caching. you say
you have 1gb of ram, and the database is 2gb.Obviously the entire
database isn't cached, but maybe your query runs fast when the table is
in memory, and they it gets swapped out of cache because some other
piece of information moves into memory. In that circumstance, it has
to load the information from disk and is therefor slow.
how busy is the system? what other programs are running on the
machine? how big (on disk) is the table in question? what kind of load
does the system have? is it a single 80gb ide drive? Even though
you have 4 CPU's a small amount of memory and bad IO system will kill
the database.
On Monday, September 15, 2003, at 05:28 PM, Joseph Bove wrote:
Stephan,
I've run explain analyze a number of times and have gotten results
between 5.5 and 7.5 seconds
Attached is a typical output
QUERY PLAN
-
Aggregate (cost=9993.92..9993.92 rows=1 width=0)
(actual time=7575.59..7575.59 rows=1 loops=1)
- Seq Scan on vetapview (cost=0.00..9771.34 rows=89034 width=0)
(actual time=0.06..7472.20
rows=88910 loops=1)
Total runtime: 7575.67 msec
(3 rows)
The only things changing are the actual time. The costs are constant.
The relpages from pg_class for vetapview (the table in question) is
8881.
At the end of this message is the exhaustive contents of
postgresql.conf. The only settings I have attempted tuning are as
follows:
tcpip_socket = true
max_connections = 100
shared_buffers = 5000
sort_mem = 8192
fsync = false
I did have shared_buffers and sort_mem both set higher originally
(15000, 32168) but decreased them in case over-utilization of memory
was the problem.
The kernel setting shmmax is set to 256,000,000 (out of 1 gig)
Regards,
Joseph
postgresql.conf
#
# Connection Parameters
#
tcpip_socket = true
#ssl = false
max_connections = 100
#superuser_reserved_connections = 2
#port = 5432
#hostname_lookup = false
#show_source_port = false
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777 # octal
#virtual_host = ''
#krb_server_keyfile = ''
#
# Shared Memory Size
#
#shared_buffers = 15000 # min max_connections*2 or 16, 8KB each
shared_buffers = 5000
#max_fsm_relations = 1000 # min 10, fsm is free space map, ~40
bytes
#max_fsm_pages = 1 # min 1000, fsm is free space map, ~6
bytes
#max_locks_per_transaction = 64 # min 10
#wal_buffers = 8# min 4, typically 8KB each
#
# Non-shared Memory Sizes
#
#sort_mem = 32168 # min 64, size in KB
sort_mem = 8192
#vacuum_mem = 8192 # min 1024, size in KB
#
# Write-ahead log (WAL)
#
#checkpoint_segments = 3# in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300 # range 30-3600, in seconds
#
#commit_delay = 0 # range 0-10, in microseconds
#commit_siblings = 5# range 1-1000
#
fsync = false
#wal_sync_method = fsync# the default varies across platforms:
# # fsync, fdatasync, open_sync, or
open_datasync
#wal_debug = 0 # range 0-16
#
# Optimizer Parameters
#
#enable_seqscan = true
#enable_indexscan = true
#enable_tidscan = true
#enable_sort = true#enable_tidscan = true
#enable_sort = true
#enable_nestloop = true
#enable_mergejoin = true
#enable_hashjoin = true
#effective_cache_size = 1000# 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)
#default_statistics_target = 10 # range 1-1000
#
# GEQO Optimizer Parameters
#
#geqo = true
#geqo_selection_bias = 2.0 # range 1.5-2.0
#geqo_threshold = 11
#geqo_pool_size = 0 # default based on tables in statement,
# range 128-1024
#geqo_effort = 1
#geqo_generations = 0#geqo_random_seed = -1 # auto-compute
seed
#
# Message display
#
#server_min_messages = notice # Values, in order of decreasing
detail:
# debug5, debug4, debug3, debug2,
debug1,
# info, notice, warning, error, log,
fatal,
# panic
#client_min_messages = notice # Values, in order of decreasing
detail:
# debug5, debug4, debug3, debug2,
debug1,
# log, info, notice, warning, error
#silent_mode = false
#log_connections = false
#log_pid = false
#log_statement = false
#log_duration = false
#log_timestamp = false
#log_min_error_statement = error # Values in order of increasing
severity:
#log_min_error_statement = error # Values in order of increasing
severity:
# debug5, debug4, debug3,
