The power of response times and execution time

04.2014 / Category: / Tags: |

The goal of the PostgreSQL optimizer is to provide you with a plan, which executes as fast as possible and returns all the data as rapidly as possible. In short: The game is about overall execution time of queries.

However, there are cases in which you are not interested in receiving the entire result as fast as possible. Just consider the following example: Imagine going through a catalog. Do you really want to read all 649 pages? Do you really want to go through 10.000 products? Not really. You open a catalog, you read the first handful of products and this was it.

So, in many cases it is not about the speed of the entire dataset but about the performance of the first couple of records.

Here is an example:

We generate a data set consisting of 10 million rows. Make sure that the data is not stored in the table sorted. Otherwise this example will not work (due to high correlation estimates).

Then we create a simple index:

Let us run a simple query now:

The point here is that PostgreSQL will sort the data. Most likely this is going to be the fastest plan here. But, if we sort the data, it actually means that the first rows of data will only be delivered to the client as soon as the entire thing has been sorted.

Going back to our example involving the catalog it would mean: We got to read and prepare the entire catalog to read just a handful of rows at the beginning. Response times will be slow and, more importantly, we would not read the entire stuff anyway.

cursor_tuple_fraction in action

In PostgreSQL a cursor can be used to fetch large amounts of data. Cursors are frequently used to do batch processing. However, there is one more important thing to know about a cursor. By default a cursor will optimize for the first 10% of data. This behavior can be changed even by setting cursor_tuple_fraction.

Here is an example showing how things can be changed:

In this case we have told the cursor to expect only a very small fraction of the data to be fetched.

We can declare the cursor now:

Fetching the row will be supersonic fast:

Instead of sorting all the data at once PostgreSQL will return the data extremely fast. Sure, looping through the entire cursor will be slower, but - there is no need to sort all the data (saving on disk I/O) and the result will be delivered without an initial delay, which can be crucial in many cases.

5 2 votes
Article Rating
Notify of
Inline Feedbacks
View all comments
CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    Would love your thoughts, please comment.x
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram