Hi all,
I have a table with +/- 5.5 million records. There is a column (field) which
has the default value of a sequence. The records get dumped to postgresql
from another database, about 50'000 records at a time, once a day. I read
these records in using the 'load' feature.
Today, I decided to re-sequence all the records, I did this with:
DROP SEQUENCE Seq_PhoneLog;
CREATE SEQUENCE Seq_PhoneLog START 1;
update phonelog set recno = nextval('seq_phonelog') + 1000000000;
update phonelog set recno = recno - 1000000000;
Each of the update statements is taking about 4 hours. I do this in two
steps so there is no problem with records with duplicate numbers which might
already exist. The recno field has a unique index on it.
I checked disk space and it went down from about 25 GB available to 9 GB
available, with a huge number of files getting created in $PGDATA/pg_xlog,
ache 16 MB in size.
Surely, there is something I can do to make this faster. Here are some of
the values I have set in postgres.conf (the rest is not set)
(the machine is a 850 MHz AMD K7 with 768 MB RAM and 2 x 36 GB Ultra
SCSI-160 for data and tmp storage and 1 x 9 GB Ultra SCSI-160 as boot
drive):
#
# Performance
#
#sort_mem = 512
shared_buffers = 1024
#fsync = true
#
# Optimizer Parameters
#
enable_seqscan = true
enable_indexscan = true
enable_tidscan = true
enable_sort = true
enable_nestloop = true
enable_mergejoin = true
enable_hashjoin = true
#ksqo = false
#geqo = true
effective_cache_size = 5000 # default in 8k pages
#random_page_cost = 4
ql_inheritance = true
#
# Deadlock
#
deadlock_timeout = 100
#
# Expression Depth Limitation
#
max_expr_depth = 10000 # min 10
#
# Write-ahead log (WAL)
#
wal_buffers = 32 # min 4
wal_files = 64 # range 0-64
wal_sync_method = fsync # fsync or fdatasync or open_sync or open_datasync
# Note: default wal_sync_method varies across platforms
wal_debug = 0 # range 0-16
commit_delay = 0 # range 0-100000
commit_siblings = 5 # range 1-1000
checkpoint_segments = 3 # in logfile segments (16MB each), min 1
checkpoint_timeout = 300 # in seconds, range 30-3600
#
# Debug display
#
#silent_mode = false
log_connections = true
log_timestamp = true
log_pid = false
#debug_level = 0 # range 0-16
#debug_print_query = false
#debug_print_parse = false
#debug_print_rewritten = false
#debug_print_plan = false
#debug_pretty_print = false
#ifdef USE_ASSERT_CHECKING
#debug_assertions = true
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
