Re: full-text indexing

> Hi,
>
> I guess that 'original author' would be me....

Yes.

>
> > > With the original author, testing was fast, but when he loaded all the
> > > data, it got very slow.  The problem was that as soon as his data
> > > exceeded the buffer cache, performance became terrible.
> >
> > How much data are we talking here?  How can one get around this buffer
> > cache problem?
>
> This is all from head, but if I remember correctly, the main table had about
> 750.000 rows, of which one varchar(25) field was fti'ed ('full text indexed'
> :),
> resulting in some 5 million rows in the fti table.
>
> wrt file sizes, I don't really remember. If you're really interested, I can
> make another setup to check this (over easter time).
>
> I'm curious: Bruce mentioned buffer cache sizes. What exactly is this buffer
> cache
> used for? I thought we relied on the OS filesystem caching to cache database
> files?
> What will increasing buffer caches give me?

The PostgreSQL shared buffers are used by the database to read/write 8k
db buffers.  The OS has buffers two, so there is some duplication.  Ours
exist in shared memory so all backends can use the information and
mark/flush them as needed.  Increasing the shared buffer cache will keep
more buffers availible, but frankly the OS buffer cache is just/more
important.  It is when the stuff is in neither cache and we have to go
to disk thousands of time for one query that things get bad.

--
  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