Re: inserting, index and no index - speed

zilch(at)home(dot)se writes:
> CREATE TABLE index_with (
> id SERIAL,
> name TEXT
> );
> CREATE INDEX name_index ON index_with(name);

> CREATE TABLE index_without (
> id SERIAL,
> name TEXT
> );

Actually, what you are comparing here is a table with two indexes to a
table with one index. Moreover, both of them incur a sequence nextval()
operation for each insert. So it's not two files updated versus one,
it's four versus three.

Also, given the small size of these tables, it's likely that most of the
updates occur in in-memory disk buffers. If you are running with fsync
on, nearly all the actual I/O per insert will be the write and fsync of
the WAL log. The time required for that is not going to be very
sensitive to the amount of data written, as long as it's much less than
one disk block per transaction, which will be true in both these cases.
You end up writing one block to the log per transaction anyway.

You might try running the ten thousand inserts as a single transaction
(do "begin" and "end" around them). It'd also be educational to try it
with fsync disabled, or with id declared as plain int not serial.

regards, tom lane