Re: Including Snapshot Info with Indexes

Hi,
I have tested with makeing this change and it is showing useful
readings. The point of introducing the indexes with snapshot is that it
should reduce the number of logical I/Os.(It may be from memory / from hard
disk). Logical I/Os are potential Physical I/Os.

On 10/20/07, Martijn van Oosterhout <kleptog(at)svana(dot)org> wrote:
>
> On Sat, Oct 20, 2007 at 09:24:07AM +0530, Gokulakannan Somasundaram wrote:
> > Hi,
> > I think i have a initial Implementation. It has some bugs and i am
> working
> > on fixing it. But to show the advantages, I want to show the number of
> > Logical I/Os on the screen. In order to show that, i tried enabling the
> > log_statement option in PostgreSQL.conf. But it shows only the physical
> > reads. What i wanted was a Logical reads count( No. of ReadBuffer
> calls,
> > which is stored in ReadBufferCount variable). So i have added this stats
> to
> > the bufmgr.c(function is BufferUsage, i suppose) to show Logical Reads
> and
> > Physical Reads. Is this a acceptable change?
>
> I'm not sure if the number of logical reads is really a useful
> measurement. I can imagine there are places that deliberatly read the
> block "logically" a few times but drop the pin in between to allow
> others access. This will skew your results as in actual usage only the
> first is likely to generate a real I/O.

If they have dropped the pin to allow other accesses, then the buffer may
lose its place in memory. So it might become a physical I/O, of course at a
lower probability. But still if we think of this from SQL tuner's
perspective, he is going to change the query slightly, or add/remove indexes
in order to verify whether he has improved the Query performance. Can we say
that he has improved the performance 99% of the time, if the SQL fired has
reduced the logical I/Os?

If your problem is cache it seems to me you should test with a table
> larger than your shared buffers and perhaps even larger than your total
> memory, since this is the case we're actually interested in.

In this case we may not know which rows of the table are in which block. Say
we fire a query, which does index scan. it might have referred to some table
block. We can't say for sure that if i change some value in the index scan,
it won't touch the same table block. This solution is perfect, if we have to
do a Load Test / Performance Test. But for SQL tuning, running a Load test
is slightly costly.

Even, if the statistic doesn't become useful in some cases, we can safely
ignore it.
I will submit my initial patch today.

Thanks,
Gokul.