Skip site navigation (1) Skip section navigation (2)

Re: Performance

From: Greg Smith <greg(at)2ndquadrant(dot)com>
To: James Mansion <james(at)mansionfamily(dot)plus(dot)com>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Claudio Freire <klaussfreire(at)gmail(dot)com>, Tomas Vondra <tv(at)fuzzy(dot)cz>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Performance
Date: 2011-04-29 18:55:49
Message-ID: 4DBB09B5.80108@2ndquadrant.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.

-- 
Greg Smith   2ndQuadrant US    greg(at)2ndQuadrant(dot)com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books


In response to

Responses

pgsql-performance by date

Next:From: Greg SmithDate: 2011-04-29 19:03:18
Subject: Re: FUSION-IO io cards
Previous:From: Wayne ConradDate: 2011-04-29 18:24:11
Subject: 8.4.7, incorrect estimate

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group