Re: performance tuning on inserts

"Peter T. Brown" <peter(at)memeticsystems(dot)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