performance modality in 7.1 for large text attributes?

(plz cc me on your replies, i'm not on pgsql-hackers for some reason.)

http://www.vix.com/~vixie/results-psql.png shows a gnuplot of the wall time
of 70K executions of "pgcat" (shown below) using a CIDR key and TEXT value.
(this is for storing the MAPS RSS, which we presently have in flat files.)

i've benchmarked this against a flat directory with IP addresses as filenames,
and against a deep directory with squid/netnews style hashing (127/0/0/1.txt)
and while it's way more predictable than either of those, there's nothing in
my test framework which explains the 1.5s mode shown in the above *.png file.

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.)

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. arbitrary-sized "text" attributes are a huge boon -- we would
never have considered using postgres for MAPS RSS (or RBL) with "large
objects". (kudos to all who were involved, with both WAL and TOAST!)

here's the test jig -- please don't redistribute it yet since there's no man
page and i want to try binary cursors and other things to try to speed it up
or clean it up or both. 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.

# This is a shell archive. Save it in a file, remove anything before
# this line, and then unpack it by entering "sh file". Note, it may
# create directories; files and directories will be owned by you and
# have default permissions.
#
# This archive contains:
#
# Makefile
# pgcat.c
#
echo x - Makefile
sed 's/^X//' >Makefile << 'END-of-Makefile'
X## Copyright (c) 2000 by Mail Abuse Prevention System LLC
X##
X## Permission to use, copy, modify, and distribute this software for any
X## purpose with or without fee is hereby granted, provided that the above
X## copyright notice and this permission notice appear in all copies.
X##
X## THE SOFTWARE IS PROVIDED "AS IS" AND INTERNET SOFTWARE CONSORTIUM DISCLAIMS
X## ALL WARRANTIES WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES
X## OF MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL INTERNET SOFTWARE
X## CONSORTIUM BE LIABLE FOR ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL
X## DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR
X## PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS
X## ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS
X## SOFTWARE.
X
X# $Id: Makefile,v 1.1.1.1 2000/12/19 04:49:51 vixie Exp $
X
XCC= gcc -Wall
XALL= pgcat
X
XLDFLAGS= -L/usr/local/pgsql/lib -L/usr/local/krb5/lib
XCFLAGS= -I/usr/local/pgsql/include
XLIBS= -lpq -lcom_err
X
Xall: $(ALL)
X
Xkit:; shar Makefile pgcat.c >kit
X
Xclean:; rm -f $(ALL) kit; rm -f *.o
X
Xpgcat: pgcat.o Makefile
X $(CC) $(LDFLAGS) -o pgcat pgcat.o $(LIBS)
X
Xpgcat.o: pgcat.c Makefile
END-of-Makefile
echo x - pgcat.c
sed 's/^X//' >pgcat.c << 'END-of-pgcat.c'
X/*
X * Copyright (c) 2000 by Mail Abuse Prevention System LLC
X *
X * Permission to use, copy, modify, and distribute this software for any
X * purpose with or without fee is hereby granted, provided that the above
X * copyright notice and this permission notice appear in all copies.
X *
X * THE SOFTWARE IS PROVIDED "AS IS" AND INTERNET SOFTWARE CONSORTIUM DISCLAIMS
X * ALL WARRANTIES WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES
X * OF MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL INTERNET SOFTWARE
X * CONSORTIUM BE LIABLE FOR ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL
X * DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR
X * PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS
X * ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS
X * SOFTWARE.
X */
X
X#ifndef LINT
Xstatic const char rcsid[] = "$Id: pgcat.c,v 1.1.1.1 2000/12/19 04:49:50 vixie Exp $";
X#endif
X
X#include <sys/param.h>
X#include <sys/types.h>
X#include <sys/stat.h>
X
X#include <stdio.h>
X#include <stdlib.h>
X#include <string.h>
X#include <unistd.h>
X
X#include <libpq-fe.h>
X
Xstatic const char tmp_template[] = "/tmp/pgcat.XXXXXX";
Xstatic const char *progname = "amnesia";
X
Xstatic int get(PGconn *, const char *, const char *, const char *,
X const char *, const char *);
Xstatic int put(PGconn *, const char *, const char *, const char *,
X const char *, const char *);
X
Xstatic void
Xusage(const char *msg) {
X fprintf(stderr, "%s: usage error (%s)\n", progname, msg);
X fprintf(stderr,
X "usage: %s get|put <dbname> <table> <key> <value> <text> [<file>]\n",
X progname);
X exit(1);
X}
X
Xint
Xmain(int argc, char *argv[]) {
X const char *pghost = NULL, *pgport = NULL, *pgoptions = NULL,
X *pgtty = NULL;
X const char *op, *dbname, *table, *key, *value, *text, *file;
X PGconn *conn;
X int status;
X
X if ((progname = strrchr(argv[0], '/')) != NULL)
X progname++;
X else
X progname = argv[0];
X if (argc < 7)
X usage("too few arguments");
X op = argv[1];
X dbname = argv[2];
X table = argv[3];
X key = argv[4];
X value = argv[5];
X text = argv[6];
X if (argc > 8)
X usage("too many arguments");
X else if (argc == 8)
X file = argv[7];
X else
X file = NULL;
X if (strcmp(op, "get") != 0 && strcmp(op, "put") != 0)
X usage("operation must be 'get' or 'put'");
X conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbname);
X if (PQstatus(conn) == CONNECTION_BAD) {
X fprintf(stderr, "%s: \"%s\": %s", progname, dbname,
X PQerrorMessage(conn));
X status = 1;
X } else if (strcmp(op, "get") == 0) {
X status = get(conn, table, key, value, text, file);
X } else {
X status = put(conn, table, key, value, text, file);
X }
X PQfinish(conn);
X return (status);
X}
X
Xstatic int
Xget(PGconn *conn, const char *table, const char *key, const char *value,
X const char *text, const char *file)
X{
X char cmd[999], ch, pch;
X const char *p;
X PGresult *res = NULL;
X int status = 0;
X FILE *fp = stdout;
X
X /* Open the output file if there is one. */
X if (file != NULL) {
X fp = fopen(file, "w");
X if (fp == NULL) {
X perror(file);
X status = 1;
X goto done;
X }
X }
X
X /* Quote the lookup value if nec'y. */
X if (strchr(value, '\'') != NULL || strchr(value, ':') != NULL)
X p = "";
X else
X p = "'";
X
X /* Send the query. */
X if (snprintf(cmd, sizeof cmd, "SELECT %s FROM %s WHERE %s = %s%s%s",
X text, table, key, p, value, p) >= sizeof cmd) {
X fprintf(stderr, "%s: snprintf overflow\n", progname);
X status = 1;
X goto done;
X }
X res = PQexec(conn, cmd);
X if (PQresultStatus(res) != PGRES_TUPLES_OK) {
X fprintf(stderr, "%s: \"%s\": %s", progname, cmd,
X PQresultErrorMessage(res));
X status = 1;
X goto done;
X }
X if (PQnfields(res) != 1) {
X fprintf(stderr, "%s: \"%s\": %d fields?\n",
X progname, cmd, PQnfields(res));
X status = 1;
X goto done;
X }
X if (PQntuples(res) != 1) {
X fprintf(stderr, "%s: \"%s\": %d tuples?\n",
X progname, cmd, PQntuples(res));
X status = 1;
X goto done;
X }
X
X /* Output the result. */
X pch = '\0';
X for (p = PQgetvalue(res, 0, 0), ch = '\0'; (ch = *p) != '\0'; p++) {
X putc(ch, fp);
X pch = ch;
X }
X if (pch != '\n')
X putc('\n', fp);
X done:
X if (fp != NULL && fp != stdout)
X fclose(fp);
X if (res != NULL)
X PQclear(res);
X return (status);
X}
X
Xstatic int
Xput(PGconn *conn, const char *table, const char *key, const char *value,
X const char *text, const char *file)
X{
X char *t, *tp, cmd[999];
X const char *p;
X PGresult *res = NULL;
X int status = 0, ch, n;
X FILE *fp = stdin, *copy = NULL;
X struct stat sb;
X size_t size;
X
X /* Open the file if there is one. */
X if (file != NULL) {
X fp = fopen(file, "r");
X if (fp == NULL) {
X perror(file);
X status = 1;
X goto done;
X }
X }
X
X /*
X * Read the file to find out how large it will be when quoted.
X * If it's not a regular file, make a copy while reading, then switch.
X */
X if (fstat(fileno(fp), &sb) < 0) {
X perror("stat");
X status = 1;
X goto done;
X }
X if ((sb.st_mode & S_IFMT) != S_IFREG) {
X char tmpname[MAXPATHLEN];
X int fd;
X
X strcpy(tmpname, tmp_template);
X fd = mkstemp(tmpname);
X if (fd < 0) {
X perror("mkstemp");
X status = 1;
X goto done;
X }
X copy = fdopen(fd, "r+");
X unlink(tmpname);
X }
X size = 0;
X while ((ch = getc(fp)) != EOF) {
X if (ch == '\\' || ch == '\'')
X size++;
X size++;
X if (copy)
X putc(ch, copy);
X }
X if (ferror(fp)) {
X perror("fread");
X status = 1;
X goto done;
X }
X if (copy) {
X if (fp != stdin)
X fclose(fp);
X fp = copy;
X copy = NULL;
X }
X rewind(fp);
X
X /* Quote the lookup value if nec'y. */
X if (strchr(value, '\'') != NULL || strchr(value, ':') != NULL)
X p = "";
X else
X p = "'";
X
X /* Construct the INSERT command. */
X n = snprintf(cmd, sizeof cmd,
X "INSERT INTO %s ( %s, %s ) VALUES ( %s%s%s, '",
X table, key, text, p, value, p);
X if (n >= sizeof cmd) {
X fprintf(stderr, "%s: snprintf overflow\n", progname);
X status = 1;
X goto done;
X }
X t = malloc(n + size + sizeof "');");
X if (t == NULL) {
X perror("malloc");
X status = 1;
X goto done;
X }
X strcpy(t, cmd);
X tp = t + n;
X while ((ch = getc(fp)) != EOF) {
X if (ch == '\\' || ch == '\'')
X *tp++ = '\\';
X *tp++ = ch;
X }
X *tp++ = '\'';
X *tp++ = ')';
X *tp++ = ';';
X *tp++ = '\0';
X
X /* Send the command. */
X res = PQexec(conn, t);
X if (PQresultStatus(res) != PGRES_COMMAND_OK) {
X fprintf(stderr, "%s: \"%s\": %s", progname, t,
X PQresultErrorMessage(res));
X status = 1;
X goto done;
X }
X if (strcmp(PQcmdTuples(res), "1") != 0) {
X fprintf(stderr, "%s: \"%s...\": '%s' tuples? (%s)\n",
X progname, cmd, PQcmdTuples(res), PQcmdStatus(res));
X status = 1;
X goto done;
X }
X
X done:
X if (fp != NULL && fp != stdin)
X fclose(fp);
X if (res != NULL)
X PQclear(res);
X return (status);
X}
END-of-pgcat.c
exit