CYBERTEC PostgreSQL Logo

Speeding up “min” and “max”

09.2013 / Category: / Tags: |

Indexes are a perfect tool to find a certain value or some kind of range in a table. It is possible to speed up a query many times by avoiding a sequential scan on a large table. This kind of behavior is widely known and can be observed in any relational database system.

Table of Contents

What is interesting to note is that indexes are not only good to search for data – they also offer a good way to provide you with sorted output.

Sorted output

Let us try to demonstrate things with a simple example. For the sake of simplicity we create a table with 100.000 rows:

The goal of this example is to show what happens if you want to find the top 5 IDs. The query is pretty simple:

All we need is an ORDER BY clause as well as a LIMIT clause. There is nothing special about this query. What is more important is the execution plan used for this query:

PostgreSQL has to scan the table completely and perform a top-N heapsort. The larger the table grows the longer this will take. In other words: You cannot do this kind of query on a very large table if you need deterministic runtimes.

An index can help:

The plan we had before needed 35 ms to perform the query. Once we have defined the index we can make use of its sorted content:

All PostgreSQL has to do now is to read the index backwards and take the first 5 rows. Note that there is no WHERE clause involved here. We merely take advantage of sorting here.

MIN and MAX

The same concept can be applied to min and max. The max value is the highest entry in the table, which is not NULL. The min value is the lowest value on the PostgreSQL table, which is not NULL.

For quite some time now the PostgreSQL optimizer is able to handle this kind of query efficiently:

The query executes well under a millisecond because all it does is scanning the index twice and return the data. This is a major benefit when it comes to performance.

----------
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.

Comments are closed.

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


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

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram