Re: your mail

>
> For each test, I will list 2 numbers here, the first being with the default
> number of buffers, the second with 256 buffers (ie. 2048K)
>
> btw. just to give all possible information: I start postgreSQL with the
> following arguments:
>
> -i -b /usr/local/pgsql/bin/postgres -S 1024 -o "-o mylogfile -F -d 1"
>
> > OK, I meant to reply to this, but forgot to.  Let's take the second query:
> >
> >    select count(*) from artist_fti where string ~ '^lling';
> >
> > Have you tried adding -B buffers.  This, I think, would help.  If you
> > don't flush the cache, how long does a second identical query take?
>
> This is 3 times the above query without trashing in between and with
> the default number of buffers:
>
>     0.030u 0.020s 0:23.41 0.2% 0+0k 0+0io 201pf+0
>     0.040u 0.010s 0:01.06 4.7% 0+0k 0+0io 184pf+0w
>     0.030u 0.040s 0:00.70 10.0% 0+0k 0+0io 184pf+0w

OK, I think this actually tells the whole story.  The query goes from 22
seconds to 0.75 seconds because all of the btree indexes are in the
buffer.   Looks like the optimizer is clearly understanding both parts
of the restriction, which is good.  No problems there.

Also seems the index fits easily in the cache.

Now my question is "What is the performance problem?"  Doesn't the
shared buffer cache keep these in memory, so the first one is slow, but
the rest are fast, or does the buffer get flushed a lot, and performance
is terrible on the first query after that.  Does going after other words
flush the cache for previous words searched?

I must say, I am surprised that the buffer cache causes it to speed up
so much.  Are these really slow disks?

In the Ingres case, the first query or two was not as quick, but they
speeded up, and kept pretty fast for the rest of the day.  Also, the
indexes where ISAM, which has a less sophisticated/less overhead way of
indexing than btree.


--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)