Re: table full scan or index full scan?

On Sun, Oct 11, 2009 at 10:01:52PM -0500, Peter Hunsberger wrote:
> This is interesting, I just ran a similar issue the other day.
> Clearly there is a wide range of read / write scenarios that Postgres
> should be able to cover. These days, I have a lot of designs leaning
> more toward the data warehouse side of the operational spectrum as
> opposed to the high transaction scenario and I specifically design DB
> management strategies with the knowledge that writes will happen far
> less than reads in our applications. Is this an area where
> optimizations are considered hard in Postrgres or hopefully, just
> something that is on the todo list but just no one has gotten around
> to yet?

We consider any optimisation that is feasible. Unfortunatly, "the
number of rows in a table" is a fairly hard number to get in the
general case because it depends on who is asking (different
transactions may get different answers).

>Similarly, are accurate table summary stats possible someday
> or are they considered close to impossible in order to eliminate race
> conditions and lock contention scenarios?

It is possible, it's just not cheap in the general case. The usual
approach is to keep a table that tracks the number of rows. By using
deltas you can make it lockfree. These are however costs most
applications do't need. If you know in your case that the data never
changes, just cache the result somewhere. That will be infinitly more
efficient than any other method.

If you are happy with estimates, they are there and are kept reasonably
uptodate.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.