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

On 8/14/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "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=#

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?