Re: inserting, index and no index - speed

Thanks Tom,
really appreciate it!

Daniel Akerud

> 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