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

On 8/14/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Lim Berger" <straightfwd007@gmail.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=#



Thanks Tom. But on a newly minted table, sure, the performance would
be great. My table now has about 3 million rows (both in MySQL and
PG).

Here's the table definition:


                             Table "public.cachedstats"
        Column         |         Type          |          Modifiers
-----------------------+-----------------------+------------------------------
 id                    | bigint                | not null
 prof_name             | character varying(20) | not null
 notes                 | text                  | not null
 inform_prof_on_change | character(1)          | not null default 'N'::bpchar

Indexes:
    "cachedstats_pkey" PRIMARY KEY, btree (id)
    "idx_cachedstats_unique_prof_name" UNIQUE, btree (alias)


How can I test the bottleneck?