The server is dual Xeon with 4Gb RAM and 10k RPM RAID 1. There is no workload, we are running test conversion hence autovacuum off. I tried with on too, to no avail. Pg version is now 8.2. Here's my pg sql config file, unabridged. hba_file = '/etc/postgresql/8.2/main/pg_hba.conf' # host-based authentication file ident_file = '/etc/postgresql/8.2/main/pg_ident.conf' # ident configuration file external_pid_file = '/var/run/postgresql/8.2-main.pid' # write an extra PID file port = 5432 # (change requires restart) max_connections = 100 # (change requires restart) unix_socket_directory = '/var/run/postgresql' # (change requires restart) ssl = true shared_buffers = 512MB work_mem = 1024MB maintenance_work_mem = 1024MB max_fsm_pages = 300000 checkpoint_segments = 60 # me: increased from 3 based on warnings effective_cache_size = 2048MB stats_row_level = on autovacuum = off # enable autovacuum subprocess? autovacuum_naptime = 120min # time between autovacuum runs datestyle = 'iso, mdy' lc_messages = 'C' # locale for system error message lc_monetary = 'C' # locale for monetary formatting lc_numeric = 'C' # locale for number formatting lc_time = 'C' # locale for time formatting escape_string_warning = off Here's my actual database schema up to names taskcomments_csv table has ~10mil rows, id - sequence-bound identity column taskid - bigint, usernick - varchar(40), and comment - text. Index on usernick. taskcomments table is the same but has userid int instead of usernick and is empty, index on userid. users has 7k rows, nick varchar(40), id int. THe following query: "INSERT INTO taskcomments (comment, userid, taskid) SELECT comment, users.uid, taskid FROM taskcomments_csv INNER JOIN users ON taskcomments_csv.usernick = users.nick ran for 9 hours before I killed it, taking 1Gb RAM, 95% CPU in ps -eo report and doing some painfully slow (~1Kb per several seconds) HD writing in df output (e.g. it's not a HDD bottleneck). Obvisouly explain analyze is not an option, explain for select predicts a runtime of less than an hour. There's virtually nothing else running on the server. There's no workload on the database. Can someone tell me what is wrong with this and what do I fix? Or how else do I diagnose it? I understand I can do it via updates within the same table and that's what I am about to do, but for other tables that is not an option cause as I have already learned dropping a varchar column after conversion and running vacuum full to get rid of its data could take days.
