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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026