Re: performance tuning on inserts - Mailing list pgsql-admin

Regarding the SQL: The query I showed there is built dynamically from a
library of queries chosen by the application user (using a web gui). For
now, I don't have any way to intelligently condense the often complex series
of operations into a single 'smart' query.

That being said, I still don't understand why doing all those inserts should
take so long since the entire table should be in memory... I am pretty sure
I've allowed enough shared_buffers.

Regarding timestamps in pg_xlog: as I understand things, if wal_buffers and
checkpoint_segments are high enough the files in pg_xlog should never be
used, right?

Thanks Again,

Peter T. Brown

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, January 28, 2002 12:02 PM
To: Peter T. Brown
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] performance tuning on inserts


"Peter T. Brown" <peter@memeticsystems.com> writes:
> All of my queries rely heavily on doing INSERT INTO. So is there some
> special behavior with insert's, where they are flushed to disk one by one?
> If I simply increase checkpoint_segments to 50 or so would this cause
> inserts to occur only in memory and be flushed to disk at a later
> time?

Increasing checkpoint_segments is a good idea if you do lots of bulky
inserts.  Basically you don't want checkpoints happening every few
seconds; at most one every couple minutes would be my recommendation.
If checkpoint_segments is too small then you're forcing frequent
checkpoints.

Whether 6 is enough is hard to tell from the data you've given.  You
could look at the file timestamps in pg_xlog to try to estimate how
often a new segment is started.  Note that there's some interaction
here: reducing the frequency of checkpoints will actually reduce the
volume of WAL traffic.

> Sample SQL:
> INSERT INTO "VisitorPointer" ("CohortGroupID","VisitorID") SELECT
> 51,"Tidbit"."VisitorID" FROM "Tidbit" WHERE "Tidbit"."CustomFieldID" = 27
> AND "Tidbit"."Value" LIKE 'asd1834%'
> CREATE TEMP TABLE temp5946 AS SELECT DISTINCT ON ("VisitorID") * FROM
> "VisitorPointer" WHERE "CohortGroupID" = 51; DELETE FROM "VisitorPointer"
> WHERE "CohortGroupID" = 51;
> INSERT INTO "VisitorPointer" SELECT * FROM temp5946; DROP TABLE temp5946;

Seems like a little work on improving your SQL wouldn't hurt either.
Couldn't the above mess be reduced to a single command?  Viz

INSERT INTO "VisitorPointer" ("CohortGroupID","VisitorID")
  SELECT DISTINCT 51,"Tidbit"."VisitorID" FROM "Tidbit"
    WHERE "Tidbit"."CustomFieldID" = 27 AND "Tidbit"."Value" LIKE 'asd1834%'

All that inserting of rows you're only going to delete a moment later is
costing you.

            regards, tom lane