Re: Performance

On 4/29/2011 1:55 PM, Greg Smith wrote:
> James Mansion wrote:
>> Does the server know which IO it thinks is sequential, and which it
>> thinks is random? Could it not time the IOs (perhaps optionally) and
>> at least keep some sort of statistics of the actual observed times?
>
> It makes some assumptions based on what the individual query nodes are
> doing. Sequential scans are obviously sequential; index lookupss random;
> bitmap index scans random.
>
> The "measure the I/O and determine cache state from latency profile" has
> been tried, I believe it was Greg Stark who ran a good experiment of
> that a few years ago. Based on the difficulties of figuring out what
> you're actually going to with that data, I don't think the idea will
> ever go anywhere. There are some really nasty feedback loops possible in
> all these approaches for better modeling what's in cache, and this one
> suffers the worst from that possibility. If for example you discover
> that accessing index blocks is slow, you might avoid using them in favor
> of a measured fast sequential scan. Once you've fallen into that local
> minimum, you're stuck there. Since you never access the index blocks,
> they'll never get into RAM so that accessing them becomes fast--even
> though doing that once might be much more efficient, long-term, than
> avoiding the index.
>
> There are also some severe query plan stability issues with this idea
> beyond this. The idea that your plan might vary based on execution
> latency, that the system load going up can make query plans alter with
> it, is terrifying for a production server.
>

How about if the stats were kept, but had no affect on plans, or
optimizer or anything else.

It would be a diag tool.  When someone wrote the list saying "AH! It
used the wrong index!".  You could say, "please post your config
settings, and the stats from 'select * from pg_stats_something'"

We (or, you really) could compare the seq_page_cost and random_page_cost
from the config to the stats collected by PG and determine they are way
off... and you should edit your config a little and restart PG.

-Andy