Re: Postgresql INSERT speed (how to improve performance)?

"Lim Berger" <straightfwd007(at)gmail(dot)com> writes:
> I have a table in MySQL with three compound indexes. I have only three
> columns from this table also in PostgreSQL, which serves as a cache of
> sorts for fast queries, and this table has only ONE main index on the
> primary key!

> INSERTing into MySQL takes 0.0001 seconds per insert query.
> INSERTing into PgSQL takes 0.871 seconds per (much smaller) insert query.

You had better provide some details, because that's completely out of
line, assuming that by "insert query" you mean insert one row. For a
comparison point, I get this on a rather old and slow machine:

regression=# create table t1 (f1 int, f2 int, f3 int,
regression(# unique(f1,f2),
regression(# unique(f2,f3),
regression(# unique(f1,f3));
NOTICE: CREATE TABLE / UNIQUE will create implicit index "t1_f1_key" for table "t1"
NOTICE: CREATE TABLE / UNIQUE will create implicit index "t1_f2_key" for table "t1"
NOTICE: CREATE TABLE / UNIQUE will create implicit index "t1_f1_key1" for table "t1"
CREATE TABLE
regression=# \timing
Timing is on.
regression=# insert into t1 values(1,2,3);
INSERT 0 1
Time: 9.048 ms
regression=# insert into t1 values(1,7,4);
INSERT 0 1
Time: 4.357 ms
regression=# insert into t1 values(11,7,5);
INSERT 0 1
Time: 3.998 ms
regression=#

regards, tom lane