Re: PG 7.0 is 2.5 times slower running a big report

> "Bryan White" <bryan@arcamax.com> writes:
> > I have recoverd the performance lost when I moved to Postgres 7.0 by
> > executing SET enable_indexscan = OFF before creating my cursors and
> > turning it back on for the inner loop query.  It may even be faster
> > then before so I am happy.
>
> OK, so it was the indexscans that were hurting.  (7.0 has new sorting
> code too, so I was a little afraid that the problem might be with the
> sorts.  Evidently not.)
>
> This suggests that at least on your setup, the default value of 4.0 for
> random_page_cost might still be too low.  I have not tried to measure
> that number on a Linux machine, just on machines with BSD-derived
> filesystems.  Maybe Linux does a lot worse with random accesses than
> BSD?  Needs looking into.

As I remember, Linux turns off file system prefetch if a seek is done.
BSD file systems turn off prefetch only if prefetched blocks remain
unused.

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