Tag: query tuning
Forcing a join order in PostgreSQL
© 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 […]
Rewrite OR to UNION in PostgreSQL queries
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 […]
Query parameter data types and performance
© 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 […]
Data warehousing: Making use of synchronized seq scans
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 […]
How to interpret PostgreSQL EXPLAIN ANALYZE output
© 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, […]
PostgreSQL: Speeding up GROUP BY and joins
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 […]
Using “lightweight” functional indexes – BRIN
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 […]
Detecting unstable execution times in PostgreSQL
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 […]
paginators – The story about voluntary pain in PostgreSQL
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 […]
Spying on slow statements with “auto_explain”
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 […]
Beating Uber with a PostgreSQL prototype
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 […]
Detecting wrong planner estimates
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 […]