Re: What gets cached? - Mailing list pgsql-performance

Did the patch that allows multiple seqscans to piggyback on each other
make it into 8.1? It might help in this situation.

BTW, if a query requires loading more than a few percent of an index
PostgreSQL will usually go with a sequential scan instead. You should
check explain/explain analyze on your queries and see what's actually
happening. If you've got stats turned on you can also look at
pg_stat_user_indexes to get a better idea of what indexes are and aren't
being used.

On Thu, Oct 27, 2005 at 03:41:10PM -0500, PostgreSQL wrote:
> Thank each of you for your replies.  I'm just beginning to understand the
> scope of my opportunities.
>
> Someone (I apologize, I forgot who) recently posted this query:
>     SELECT oid::regclass, reltuples, relpages
>     FROM pg_class
>     ORDER BY 3 DESC
>
> Though the application is a relatively low-volume TP system, it is
> structured a lot like a data warehouse with one primary table that
> everything else hangs off.  What the query above shows is that my largest
> table, at 34 million rows, takes almost 1.4 million pages or 10+ Gb if my
> math is good.  The same table has 14 indexes, totaling another 12Gb.  All
> this is running on a box with 4Gb of memory.
>
> So what I believe I see happening is that almost every query is clearing out
> memory to load the particular index it needs.  Hence my "first queries are
> the fastest" observation at the beginning of this thread.
>
> There are certainly design improvements to be done, but I've already started
> the process of getting the memory increased on our production db server.  We
> are btw running 8.1 beta 3.
>
> ""Steinar H. Gunderson"" <sgunderson@bigfoot.com> wrote in message
> news:20051024153248.GA24601@samfundet.no...
> > On Mon, Oct 24, 2005 at 11:09:55AM -0400, Alex Turner wrote:
> >> Just to play devils advocate here for as second, but if we have an
> >> algorithm
> >> that is substational better than just plain old LRU, which is what I
> >> believe
> >> the kernel is going to use to cache pages (I'm no kernel hacker), then
> >> why
> >> don't we apply that and have a significantly larger page cache a la
> >> Oracle?
> >
> > There have (AFAIK) been reports of setting huge amounts of shared_buffers
> > (close to the total amount of RAM) performing much better in 8.1 than in
> > earlier versions, so this might actually be okay these days.
> >
> > I haven't heard of anybody reporting increase setting such values, though.
> >
> > /* Steinar */
> > --
> > Homepage: http://www.sesse.net/
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: Don't 'kill -9' the postmaster
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461