Re: INSERT performance - Mailing list pgsql-general

DCorbit@connx.com ("Dann Corbit") wrote in message
news:<D90A5A6C612A39408103E6ECDD77B829408C2E@voyager.corporate.connx.com>...
> Cygwin? It will be faster under Linux.

I agree, I would also expect it to be faster under Linux. Would you
expect that the performance under Linux should be an order of
magnitude faster? I know that Cygwin I/O goes through something akin
to a translation layer, but I don't know how much of a performance hit
I should expect from that.

> 500 records in 12 seconds is about 42/second.  Hard to know if that is
> good or bad.  Is the machine under heavy use?  Are the records extremely
> long?

No one else is using the machine, and the records are short (at most
around 1k each).

> You can still use the copy command as an API.  It will be faster than
> the inserts, but there are (of course) caveats with its use.
> http://developer.postgresql.org/docs/postgres/libpq-copy.html

Thanks, I'll look into it.

> Provide the SQL that defines the table and its indexes.

Here is the approximate SQL statement (I cannot provide the original
statement for intellectual property reasons):

CREATE TABLE data (
    id char(32) NOT NULL, -- auto-generated from PHP using md5(...)

    -- the fieldN fields below have different lengths
    field1 varchar(5),
    field2 varchar(50),
    field3 varchar(10),
    ...
    field 20 varchar(255),

    PRIMARY KEY (id)
);

CREATE INDEX idx_field1 ON data(field1);
CREATE INDEX idx_field2 ON data(field2);
...
CREATE INDEX idx_field20 ON data(field20);

> Provide the cardinality of the table.

The table is empty (cardinality = 0).

> Provide the average machine load during the insert operation.

I will have to measure this and get back to you with it in a future
post. Here is what I can say from memory right now:

* If I do the INSERT with the indexes enabled, the HDD thrashes
visibly (audibly?) and the operation takes about 12 seconds.

* If I drop the indices, do the INSERT, and re-create the indices, the
HDD no longer thrashes, and the operation takes about 3-4 seconds.

> Probably, you can get better answers if you provide more information.

I agree -- I hope the information above is more illuminating.

> What kind of disk drives do you have on your machine?  (More
> importantly, where does PostgreSQL data reside?)

I have an ATA-100 7200 RPM HDD. The PostgreSQL data resides on this
drive (which also contains the Cygwin installation).

Thanks again,

Razvan.