Received: from icebox.venux.net (icebox.venux.net [216.120.166.10]) by hub.org (8.10.1/8.10.1) with ESMTP id e5OILN142503 for ; Sat, 24 Jun 2000 14:21:27 -0400 (EDT) Received: from doot (usr1-ip010-grr.wmis.net [209.176.193.60]) by icebox.venux.net (Postfix) with SMTP id AAD8126208 for ; Sat, 24 Jun 2000 14:27:40 -0400 (EDT) Message-ID: <000901bfde09$d541e280$0300000a@doot.org> From: "Mitch Vincent" To: Subject: More full text index.. Date: Sat, 24 Jun 2000 14:27:19 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2615.200 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200 X-Archive-Number: 200006/218 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