Re: inserting, index and no index - speed

The test script that set up the tables is the following:

---

/* Cleanup */

DROP SEQUENCE index_with_id_seq;
DROP SEQUENCE index_without_id_seq;

DROP INDEX name_index;

DROP TABLE index_with;
DROP TABLE index_without;

/* Create a table with an index */

CREATE TABLE index_with (

id SERIAL,
name TEXT

);

CREATE INDEX name_index ON index_with(name);

/* Create a table without an index */

CREATE TABLE index_without (

id SERIAL,
name TEXT

);

---

This is run just before it is tested,
then I have this little C++ program that inserts N rows into the tables, and
meassures how long it takes.

A DELETE * FROM table (both tables) followed by a VACCUUM is also run
before each test run (which consists of regular INSERT statements).

Do I do anything wrong?

The postmaster (7.1.2) is run with then current Debian testing/unstable
standard options.

Daniel Akerud

> > I just noticed that inserting 10000 tuples in an indexed table took exactly
> > the same amount of time as inserting 10000 tuples in a non-indexed table
> > (194 seconds). Why is this? The difference in MySQL is about 50% longer in
> > an indexed table.
>
> Surprises me too. Which PG version, and what are the test conditions
> exactly? (Table and index declarations; is table empty initially;
> how is backend being driven, and what commands are issued exactly?
> How many shared buffers, platform, etc)
>
> Under PG 7.1, it's possible that your test caused no actual I/O except
> to the WAL log ... but I'd still think that the volume of WAL I/O
> would be greater when writing an index.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>