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
