Re: How does PG know if data is in memory?

Fabrício dos Anjos Silva wrote:
> If someone could point good books about PG tuning, I would appreciate
> that. I found some yet to be released books about PG 9. Any comments
> about them?

The largest treatment of the subject already in print I'm aware of is in
the Korry and Susan Douglas "PostgreSQL" book from 2005, which has
around 30 pages convering PostgreSQL 8.0. That material is very good
and much of it still applies, but it's mostly theory without many
examples, and there have been a lot of changes to the optimizer since
then. There's a good talk by Robert Haas on this subject too that's
quite current, you can find his slides at
http://sites.google.com/site/robertmhaas/presentations and a recording
of one version of him giving it is at
http://www.pgcon.org/2010/schedule/events/208.en.html

My "PostgreSQL 9.0 High Performance", due out later this month if things
continue on schedule, has about 80 pages dedicated to indexing and query
optimization (waiting for final typesetting to know the exact count).
The main difference with what I do compared to every other treatment
I've seen of this subject is I suggest a sample small but not trivial
data set, then show plans for real queries run against it. So you
should be able to duplicate the examples, and then tinker with them on
your own system to see their plans change as you adjust parameters to
follow along. That really is the only way to gain expertise here.
Starting with the troublesome queries from your live system will work
just as well for that, once you get familiar with enough of the basics.

I'd suggest taking a look and listen to Robert's talk for now, that will
get you started in the right direction, and combine it with reading all
of the documentation in the manual on this subject. That should keep
you busy for a while, and by the time you're done you may find my book
is available too.

--
Greg Smith, 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
Author, "PostgreSQL 9.0 High Performance" Pre-ordering at:
https://www.packtpub.com/postgresql-9-0-high-performance/book