Hi, I am wondering about bad INSERT performance compared against the speed of COPY. (I use 7.2.2 on RedHat 7.2)
I have a table with about 30 fields, some constraints, some indexes, some foreign key constraints. I use COPY to import old data. Copying about 10562 rows takes about 19 seconds. For testing I have writtin a simple function in PL/pgSQL that inserts dummy records into the same table (just a FOR loop and an INSERT INTO ...). To insert another 10562 rows takes about 12 minutes now!!! What is the problem with INSERT in postgresql? I usually don't compare mysql and postgresql because mysql is just playing stuff, but I have think that the insert performance of mysql (even with innodb tables) is about 10 times better than the insert performance of postgresql. What is the reason and what can be done about it? Best Regards, Michael P.S: Perhaps you want to know about my postgresql.conf # # Shared Memory Size # shared_buffers = 12288 # 2*max_connections, min 16 max_fsm_relations = 100 # min 10, fsm is free space map max_fsm_pages = 20000 # min 1000, fsm is free space map max_locks_per_transaction = 64 # min 10 wal_buffers = 8 # min 4 # # Non-shared Memory Sizes # sort_mem = 4096 # min 32 (in Kb) vacuum_mem = 16384 # min 1024 # # Write-ahead log (WAL) # wal_files = 8 # range 0-64, default 0 wal_sync_method = fdatasync # the default varies across platforms: # # fsync, fdatasync, open_sync, or open_datasync fsync = true ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
