Estimating query progress
Channel: Andrea Ross
Date: 31/05/2013

Theory and practice of query progress indication


Your query has been running for 70 hours. Should you kill it now or ignore angry calls for a few more hours and hope it returns the result?

A question many a DBA have asked themselves. This talk will try to cover some of the techniques the database system could use in order to make decisions like that easier. We'll describe an approach based on existing research papers and report on the attempt of implementing it in a useful way inside PostgreSQL.

There is ample scientific literature about reporting query progress in relational database systems. Some of the papers published even mention implementations in PostgreSQL. The practicalities, however, are often skimmed over.

The talk will start by describing a method for calculating a progress indicator for running queries proposed by Surajit Chaudhuri, Vivek Narasayya, and Ravi Ramamurthy in their 2004 SIGMOD paper. We'll try to see how the terms used in the paper translate to modern PostgreSQL and what practical challenges lie before a hopeful implementer. We'll continue with a demonstration of a module that could be grown into a useful progress indicator solution.

The topic will also be an excuse for a little excursion through the PostgreSQL executor and its specific behaviour that needs to be accounted for when calculating query progress. We'll try to give the listeners a basic understanding of how the executor works and familiarize them with nomenclature used in that subsystem.