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: <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > 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 [EMAIL PROTECTED] > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 6: Have you searched our list archives? > > > > > > http://www.postgresql.org/search.mpl > > > > > > > > > > > --- > Daniel Åkerud, [EMAIL PROTECTED] > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > --- Daniel Åkerud, [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
