Received: from candle.pha.pa.us (pgman@nav-43.dsl.navpoint.com [162.33.245.46]) by hub.org (8.10.1/8.10.1) with ESMTP id e5OIYB167400 for ; Sat, 24 Jun 2000 14:34:12 -0400 (EDT) Received: (from pgman@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id OAA27704; Sat, 24 Jun 2000 14:33:30 -0400 (EDT) From: Bruce Momjian Message-Id: <200006241833.OAA27704@candle.pha.pa.us> Subject: Re: More full text index.. In-Reply-To: <000901bfde09$d541e280$0300000a@doot.org> "from Mitch Vincent at Jun 24, 2000 02:27:19 pm" To: Mitch Vincent Date: Sat, 24 Jun 2000 14:33:30 -0400 (EDT) CC: pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL77 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Archive-Number: 200006/219 I would check with EXPLAIN to see when indexes are being used. [ Charset ISO-8859-1 unsupported, converting... ] > I hadn't concentrated on the INSERT/UPDATE/DELETE speed of this until today > and I find that it's amazingly slow. Of course the time it takes is relative > to the size of the text but still, almost a minute to delete one record on a > Dual celeron 600 with 256 Megs of RAM and an ATA/66 7200 RPM 30 GIG hard > drive... INSERTs seem to be quite a bit faster (which puzzles me) but > they're still 10-20 seconds for a single record... UPDATEs seems very fast > (a few seconds). > > I do have a lot of stop works in fti.c, however when I imported the 10,000 > text files into the data base it was super fast (before I created indexes) > so I'm assuming that the indexes are slowing down the INSERTs UPDATEs and > DELETEs, which is expected I think? The database is VACUUMed on a regular > basis (and VACUUM ANALYZEed as well). > > I'd rather have the fast search than the fast data entry, I just want to be > absolutely sure that I can't do anything to speed things along.. > > If I run PGOPTIONS="-d2 -s" psql databasename > > I get this in the logs on an INSERT -- it doesn't appear to give any stats > on the queries that the function called by the fti trigger is doing.. > > > --Here is my insert query (20k of text) -- > query: INSERT INTO resumes_fti (string, id) VALUES ($1, $2) > ! system usage stats: > ! 0.644167 elapsed 0.380151 user 0.126785 system sec > ! [0.387579 user 0.149069 sys total] > ! 9/2 [13/2] filesystem blocks in/out > ! 0/2228 [0/2459] page faults/reclaims, 0 [0] swaps > ! 0 [0] signals rcvd, 0/0 [3/3] messages rcvd/sent > ! 9/4 [16/7] voluntary/involuntary context switches > ! postgres usage stats: > ! Shared blocks: 20 read, 0 written, buffer hit rate > = 99.77% > ! Local blocks: 0 read, 0 written, buffer hit rate > = 0.00% > ! Direct blocks: 0 read, 0 written > CommitTransactionCommand > proc_exit(0) > > Like I said, I just need to know if this is expected or if there might be > something (anything) I can do to speed it up.. It's going to be running on a > damn fast machine so I'm sure that these times are going to get smaller, if > not from just brute force. > > Thanks guys! > > -Mitch > > > -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026