From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Mitch Vincent <mitch@venux.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: More full text index..
Date: Sat, 24 Jun 2000 14:33:30 -0400 (EDT)
Message-ID: <200006241833.OAA27704@candle.pha.pa.us> (raw)
In-Reply-To: <000901bfde09$d541e280$0300000a@doot.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: pgman@candle.pha.pa.us, mitch@venux.net
Subject: Re: More full text index..
In-Reply-To: <200006241833.OAA27704@candle.pha.pa.us>
References: <000901bfde09$d541e280$0300000a@doot.org> <200006241833.OAA27704@candle.pha.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox