© Laurenz Albe 2023 Different from many other database systems, PostgreSQL does not support query hints. That makes it difficult to force the hand of the query planner when it comes to a certain join order that you know to be good. This article explains how you can influence execution plans in PostgreSQL. Why no […]

Rewriting OR is not always the best solution © Laurenz Albe 2022 In my article that reviles OR, I showed how in certain cases, it is possible to rewrite OR in a WHERE condition to a longer query with UNION that can perform much better (the “ugly” OR). Now people have asked me repeatedly why […]

© Laurenz Albe 2022 Recently, I could help solve a “mysterious” performance problem for a customer. The problem turned out to be a badly chosen query parameter type, which led to poor query performance. After working on this customer’s case, I realized that the meaning of the data type of a query parameter is not […]

PostgreSQL contains some hidden gems which have been around for many years and help to silently speed up your queries. They optimize your SQL statements in a clever and totally transparent way. One of those hidden gems is the ability to synchronize sequential scans. Actually, this feature has been around for 15+ years, but has […]

© Laurenz Albe 2021 UPDATED July 2023: EXPLAIN ANALYZE is the key to optimizing SQL statements in PostgreSQL. This article does not attempt to explain everything there is to it. Rather, I want to give you a brief introduction, explain what to look for and show you some helpful tools to visualize the output. Also, […]

A couple of weeks ago I have seen a horribly designed, 370 billion (with a “b”) row Oracle database monster running some sort of aggregations. Due to the sheer amount of data I naturally thought about how I would implement the same thing in PostgreSQL. What I noticed is that most people would actually implement […]

By Kaarel Moppel – Using BRIN to improve query performance – This is a quick performance tip for all those wrestling with occasional slow queries in PostgreSQL. There’s a quite simple trick available that many people don’t know about, that can be applied at “low cost” when slow queries are caused by poor planner estimates […]

Do you have queries with unstable execution times? You have a query which is sometimes slow and sometimes just lightning fast. The question is usually: Why? Maybe this little post can be inspiring and shade some light one some of your performance issues, which might bug you in your daily life. Reasons for unstable execution […]

It happens on a quite regular basis that people contact the CYBERTEC PostgreSQL Support Desk to get  support for a pretty common thing: Paginators on websites. What is the main issue? How hard can be it to display simple tables after all? A typical scenario with paginators Suppose you want to display a table on […]

PostgreSQL has tons of useful features and so it is somehow inevitable that some of it gets forgotten every now and then. In my case that’s exactly what happens a lot with a cool piece of functionality called “auto_explain”, which is a module in the contrib package. So I thought I’ll just write a short […]

The other day I got a link to an interesting post published by Uber, which has caught our attention here at Cybertec: https://eng.uber.com/go-geofence The idea behind geo-fencing is to provide information about an area to users. Somebody might want to find a taxi near a certain location, or somebody might simply want to order a […]

In 99% of all cases the PostgreSQL planner does a perfect job to optimize your queries and to make sure that you enjoy high performance and low response times. The infrastructure ensuring this is both sophisticated as well as robust. However, there are some corner cases which can turn out to be quite nasty. In […]