Re: inserting, index and no index - speed - Mailing list pgsql-general

I just rerun the application to confirm that it was really like that. So,
using the test-environment previously described i got the following output:

Database vacuumed
pg: Trying 1000 inserts with indexing on...
Time taken: 24 seconds

pg: Trying 1000 inserts with indexing off...
Time taken: 22 seconds

Database vacuumed
pg: Trying 10000 inserts with indexing on...
Time taken: 220 seconds

pg: Trying 10000 inserts with indexing off...
Time taken: 220 seconds

Daniel Akerud

>
> 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@home.se>
> > To: <pgsql-general@postgresql.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@postgresql.org
> > > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 6: Have you searched our list archives?
> > >
> > > http://www.postgresql.org/search.mpl
> > >
> >
> >
>
>
>
> ---
> Daniel Åkerud, zilch@home.se
>
>
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>



---
Daniel Åkerud, zilch@home.se