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@postgresql.org
>
