Re: performance tuning on inserts

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Peter T(dot) Brown" <peter(at)memeticsystems(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: performance tuning on inserts
Date: 2002-01-28 20:01:39
Message-ID: 22285.1012248099@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

"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

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Peter T. Brown 2002-01-28 20:22:55 Re: performance tuning on inserts
Previous Message Andy Ruhl 2002-01-28 19:40:02 Re: Sample database for me to practice with?