Re: Postgresql INSERT speed (how to improve performance)? - Mailing list pgsql-general

On 8/14/07, Lim Berger <straightfwd007@gmail.com> wrote:
> 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?
>



Btw, the query is as simple as:

INSERT INTO cachedstats
    (id, prof_name, notes, inform_prof_on_change)
VALUES
    (3190087, 'Lim Berger', '.....text of about 1000 chars', 'Y');

I am testing through PHP microtime function. The query is administered
through pg_query() function of PHP. I know there could be some latency
coming in from the PHP's PG functions' overhead, but not such an order
of magnitude different from "mysqli_query"!  (I hope -- because this
is quite a common real-world situation I would think).