Re: inserting, index and no index - speed

Yes, actually...
forgot to say that... VACUUM & VACUUM ANALYZE before each test run...

Thanks

Daniel Akerud

> Did you VACUUM ANALYZE as well, after you created the tables/indexes?
>
> ----- Original Message -----
> From: <zilch(at)home(dot)se>
> To: <pgsql-general(at)postgresql(dot)org>
> Sent: Sunday, June 10, 2001 2:15 PM
> Subject: Re: [GENERAL] 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
> > >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://www.postgresql.org/search.mpl
> >
>
>

---
Daniel Åkerud, zilch(at)home(dot)se