SQL Performance Optimization: Methods for Identifying Slow Queries
By
freekmurze
The kind of bagel that ruins lesser bagels for you.
Summary
This technical article provides a practical guide on how to identify slow SQL queries that need optimization for performance improvements. It covers various methods and tools for detecting problematic queries, with the author sharing their real-world experience of significantly improving dashboard and API performance through SQL optimization. The post is part one of a series, focusing on identification techniques before moving to actual fixes in the next installment.
Key quotes
· 4 pulledWe massively improved the performance of the dashboard & website by optimizing some of our SQL queries
In this post, we'll share how we identified the queries that needed work
We'll cover the basics and gradually work our way up to the more advanced/complex ways of identifying slow queries
This post covers tips and tricks for performance tweaking your SQL queries by first identifying which queries need work
You might also wanna read
PostgreSQL Double Buffering: Why OS RAM and Buffer Cache Compete and How the 25% Rule Helps
This article explains the problem of double buffering in PostgreSQL, where the OS filesystem cache and PostgreSQL's shared_buffers both cach
Optimizing Top K Query Performance in PostgreSQL: Challenges and Solutions
This technical article examines the challenges of optimizing Top K queries in PostgreSQL databases, where 'Top K' refers to retrieving the K
paradedb.com·2mo ago
Optimizing Geospatial SQL Queries with H3 Indexes for 400× Performance Gains
The article explains how to dramatically speed up geospatial SQL queries (geo joins) using H3 indexes. It describes the performance problems
floedb.ai·3mo ago
Performance Optimization: Replacing Protobuf with Direct C-to-Rust Bindings in PgDog PostgreSQL Proxy
The article details how PgDog, a PostgreSQL proxy written in Rust, replaced Protobuf serialization with direct C-to-Rust bindings to achieve
pgdog.dev·4mo ago
Advanced PostgreSQL Optimization Techniques Beyond Conventional Methods
This article presents unconventional optimization techniques for PostgreSQL databases, moving beyond standard approaches like query rewritin
hakibenita.com·4mo ago
SQLite Query Optimization: Using Uncorrelated Subqueries to Skip Correlated Subqueries for Performance Gains
The article describes a SQLite query optimization technique where an uncorrelated scalar subquery is used to skip expensive correlated subqu
emschwartz.me·5mo ago