Re: PostgreSQL 9.0.4 blocking in lseek?

On 10/26/11 8:47 AM, Sören Meyer-Eppler wrote:
> A fairly complex insert query on an empty destination table will run for
> an indefinite amount of time (we waited literally days for the query to
> complete). This does not happen every time we run the query but often.
> Now ordinarily I'd assume we did something wrong with our indices or
> query, but the execution plan looks sane and, more tellingly, we have:
> - no CPU load
> - no network traffic
> - no disk I/O
> - no other load on the server except this single query
> and strace displaying a seemingly endless list of lseek calls.

Hmmmm.  If you were on Solaris or OSX, I'd say you'd hit one of their
I/O bugs which can cause endless lseeks for individual disk pages.
However, I've never seen that particular pattern on Linux (other I/O
bugs, but not that one).

Questions:
(1) is it *only* that query?
(2) is there some reason you might have excessive disk fragmentation,
like running on a VM?

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com