> anybody know what i could be doing wrong? (i'm also wondering why SELECT
> takes ~250ms whereas INSERT takes ~70ms... seems counterintuitive, unless
> TOAST is doing a LOT better than i think.)
I would think that this is entirely due to planning the query. An INSERT
has no decisions to make, whereas a SELECT must decide among a variety
of possible plans. To hand-optimize selects, you can set some parameters
to force only some kinds of plans (such as index scan) but in general
you will need to remember to unset them afterwards or you run the risk
of bizarrely inappropriate plans for other queries in the same session.
> furthermore, are there any plans to offer a better libpq interface to INSERT?
> the things i'm doing now to quote the text, and the extra copy i'm maintaining,
> are painful.
What exactly are you looking for in "better"? Is it just the quoting
issue (a longstanding problem which persists for historical reasons :(
> ... but if someone can look at my code (which i'm running
> against the 7.1 bits at the head of the pgsql cvs tree) and at the *.png file
> and help me enumerate the sources of my stupidity, i will be forever grateful.
Possible causes of the 1.5s "mode" (at least as a starting point):
o task scheduling on your test machine (not likely??)
o swapping/thrashing on your test machine (not likely??)
o WAL fsync() log commits and cleanup (aggregate throughput is great,
but every once in a while someone waits while the paperwork gets done.
Waiting may be due to processor resource competition)
o Underlying file system bookkeeping from the kernel. e.g. flushing
buffers to disk etc etc.
- Thomas
