Skip site navigation (1) Skip section navigation (2)

More full text index..

From: "Mitch Vincent" <mitch(at)venux(dot)net>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: More full text index..
Date: 2000-06-24 18:27:19
Message-ID: 000901bfde09$d541e280$0300000a@doot.org (view raw or flat)
Thread:
Lists: pgsql-sql
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



Responses

pgsql-sql by date

Next:From: Bruce MomjianDate: 2000-06-24 18:33:30
Subject: Re: More full text index..
Previous:From: K ParkerDate: 2000-06-24 05:14:42
Subject: Re: Wildcard in date field

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group