Make a Slow SQL Query Fast: Read the Execution Plan, Fix the Index, Rewrite the Query

Warm Cache vs Cold Cache: Why the Second Run Lies to You CASE Statements in SQL for Conditional Logic When the Planner Has No Statistics It Just Guesses: Functions, LIKE and Range Conditions External Merge Sort Expression Index VACUUM Reclaims Dead Rows, autovacuum Does It for You, and VACUUM FULL Rewrites the Table Merge Join Lossy Bitmap Blocks: When the Bitmap Remembers Pages, Not Rows (Recheck Cond) Skipping to Page 900 With an Offset Makes the Engine Produce and Discard 18,000 Rows GROUP BY and Aggregation Functions in SQL API Pagination Structured Logging SELECT * Costs You the Index-Only Scan Network Round-Trip Latency A Transaction Left Open Across a Call You Do Not Control B-Tree Skip Scan Lets an Index Serve a Query That Omits the Leading Column Knowing When to Stop Optimising: Good Enough Versus a Capacity Problem Memoize and Materialize: Reusing Work Inside a Nested Loop Why a Columnar Analytics Engine Does Not Play by These Rules Reproduce a Slow Query with Realistic Data Volume Before You Touch It Correlated Columns Break the Planner's Assumption That Conditions Are Independent String Pattern Matching with Wildcards in SQL Correlated Subquery Rewriting Physical Row Order and Correlation: Why CLUSTER Can Make an Index Scan Cheaper Database Transaction ACID Transactions A Row Lock Is the Mechanism, and the Writer Queued Behind It Is the Cost Sargable Predicate count(*) Reads Every Row, and the Cheaper Answers That Replace It Change One Thing, Then Prove It with the Plan and the Clock A Join to the Many Side Multiplies Rows and Quietly Doubles Your SUM Estimated Rows vs Actual Rows, and Why loops Multiplies Everything You See Cursor-Based Pagination Descending and Mixed-Direction Indexes Serve ORDER BY Without a Sort A Prepared Statement Switches to a Generic Plan After Five Runs Negations and Not-Equal Conditions Rarely Earn an Index Access Latency Versus Capacity in Storage Devices Rows Removed by Filter: Proof the Engine Read Rows It Threw Away Why NOT IN With a NULL in the List Returns No Rows at All Run ANALYZE After a Bulk Load or the Planner Is Guessing Index Kinds Beyond the B-Tree, and When a B-Tree Cannot Help Common Table Expressions (CTEs) for Query Organization Reading Many Pages in Order Can Beat Reading Fewer Pages at Random Filter Rows Before the Join, Not After Build the Habit of Reading Query Plans on Queries That Are Not Slow Skewed Data: The Average Row Count Is Not the Row Count for Your Value Overriding the Planner's Distinct-Value Count When It Misjudges a Column SQL WHERE Clause Archiving Old Rows: The Cheapest Index Is a Smaller Table Database Normalization Join Order Optimization What Each Join Type Must Preserve: INNER, LEFT, RIGHT and FULL ORDER BY RANDOM() Sorts the Whole Table to Pick One Row Aggregate on the Join Key Before Joining the Wide Table Sequential Versus Random Disk Access Cost Write Amplification: One Row Change Can Mean Nine Index Writes Nested Loop Join Log the SQL Your ORM Actually Sends Before You Tune Anything ORDER BY Without LIMIT Makes the Engine Sort Every Row You Produced Gather and Parallel Query: Why Actual Rows Are Per Worker, Not Per Query WHERE Filters Rows Before Grouping and HAVING Filters Groups After SQL Subquery Foreign Key Constraints in Relational Databases Planner Cost Numbers Are Made-Up Units, Not Milliseconds See What a Session Is Waiting On: the Activity View and Its wait_event Column Which Join Algorithm the Planner Picks, and the Input Shape Each One Wants Sorted Order in a List of Values Table Statistics The Diagnostic Method: Reproduce, Measure, Plan, Hypothesise, Fix, Verify, Guard Row Estimate Errors Multiply Across Joins, So Deep Plans Go Wrong Fastest EXISTS Is a Yes-or-No Test That Can Stop at the First Matching Row Program Performance Profiling and Benchmarking Latency UNION Removes Duplicates and UNION ALL Does Not, and That Difference Costs a Sort CREATE STATISTICS Teaches the Planner That Two Columns Move Together Bitmap Index Scan and Bitmap Heap Scan: Collecting Row Locations Before Reading Pages Hash Table Parameter Sniffing: One Unusual First Value Poisons the Cached Plan Database Page Correlated Subquery Composite Index Column Order ORDER BY With LIMIT Is Fast Only When an Index Supplies the Order A SQL Result Is a Bag of Rows Until You Ask for DISTINCT Buffers in a Plan: shared hit Means Memory, shared read Means Disk Index Searches: How Many Times the Engine Descended the Index (PostgreSQL 18) The CTE Materialization Fence: MATERIALIZED and NOT MATERIALIZED Big-O Notation for Algorithm Time Complexity MySQL Optimizer Hints and Histograms When the Plan Needs a Nudge Every Extra Index Is a Tax on Every Insert, Update and Delete A Database Index Is a Second, Ordered Copy of Some Columns, and Every Write Pays to Maintain It Wrapping a Column in a Function Puts Its Index Out of Reach The Statement Statistics Extension: Find Which Query Shape Costs the Most Total Time Lower the Random Page Cost on SSDs So Index Scans Stop Looking Expensive TOAST: How PostgreSQL Stores Wide Column Values Out of Line SQL Window Function Find the Indexes Nobody Uses Before You Add Another One Index Bloat and When REINDEX Brings the Speed Back An Index Lookup in PostgreSQL Ends With a Trip to the Heap Page LIMIT Without ORDER BY Gives You Arbitrary Rows, Not the First Ones Cost-Based Query Optimizer Offset-Based Pagination SQL Injection Prevention Techniques and Prepared Statements A Materialized View Precomputes an Expensive Query and Bills You for the Refresh Relational Database Covering Index Database Index B-Tree auto_explain: Capture the Real Plan of a Slow Query in Production Structured Query Language (SQL) Dialects One Screen Can Become a Thousand Statements Without a Line of SQL in Sight Sort and Incremental Sort in a Plan, and When an Index Removes the Sort Partial Index Stale Statistics After a Bulk Load Make the Planner Plan for an Empty Table Binary Search Data Types and Schemas in SQL LIMIT Changes the Plan: Why the Database Optimises for the First Rows The Slow Query Log: Catching Slow Statements in Production With a Minimum Duration Threshold Reading a MySQL Plan: EXPLAIN ANALYZE and FORMAT=TREE on InnoDB Implicit Type Conversion Little's Law Ties Throughput, Concurrency and Latency Together A Cache Is a Second, Older Copy, So Caching Anything Is Agreeing to Be Wrong for a While Eager Loading: Fetch the Related Rows Up Front, by JOIN or by Second Query Aggregate Functions Collapse Rows and Window Functions Keep Them Moving a Predicate From ON to WHERE Breaks a LEFT JOIN statement_timeout and lock_timeout Put a Ceiling on the Damage The Escalation Ladder: Rewrite First, Index Second, Everything Else After HOT Updates and fillfactor: When an Update Can Skip Touching Every Index Finding the Step That Actually Cost You: a Node's Own Time vs Its Children's The Write-Ahead Log (WAL) Makes a Write Cost More Than a Read How NULL Behaves in COUNT, GROUP BY and DISTINCT OR Across Two Columns Blocks One Index: Split It Into UNION ALL ORDER BY Sorting Mechanisms in SQL Table and Index Bloat: When Your Pages Are Mostly Empty Space Sequential Scan vs Index Scan A Denormalized Counter Column Trades Write Cost for Read Speed The Join Collapse Limit: When the Planner Stops Searching Join Orders Getting a Plan Without Running the Query, and When EXPLAIN ANALYZE Is Unsafe Comparing Several Columns at Once With a Row Value Comparison Turn enable_seqscan Off as a Diagnostic, Never as a Fix Read the Execution Plan to See Which Access Path You Got, and Time It to See Which Step Cost You A DISTINCT That Hides a Row-Multiplying Join The Settings Behind Every Cost Estimate: Page Costs, Tuple Cost and Effective Cache Size N+1 Query Problem EXISTS, IN, and the NOT IN Trap When the Subquery Returns NULL Connection Pooling A Transaction Holds Its Connection for Its Entire Duration Predicate Selectivity Partition Pruning The Logical Order a SELECT Is Evaluated In, From FROM to LIMIT Guard a Fix So the Slow Plan Cannot Come Back Quietly Index Every Foreign Key on the Child Table An Index Only Helps When the Value You Filter On Is Rare Enough Object-Relational Mapping The Column Order of a Multi-Column Index Decides Which Statements It Can Serve Three-Valued Logic and NULL Handling in SQL Cache Invalidation The Heap: Where PostgreSQL Stores Rows, and What a ctid Points To Querying a SQLite Database from Python with the sqlite3 Module work_mem Is Per Operation, Not Per Query When a Cache Is the Right Fix and When It Hides a Slow Query Hash Join A Read Replica Adds Read Capacity Only, and Your Code Must Choose to Use It Multiversion Concurrency Control (MVCC) Keeps Old Row Versions, So Updates Leave Dead Rows Table Partitioning Batch Many Single-Row Lookups Into One IN-List Query Buffer Hit Versus Disk Read: Why the Same Query Is Fast the Second Time A LIKE Pattern That Starts With a Wildcard Cannot Use a B-Tree Index Row Width Decides How Many Rows Fit on a Page, and How Many Pages Your Query Reads Add or Retire an Index Without Taking the Table Down Plan Visualisers: Letting a Tool Point at the Expensive Node How Closely a Column Matches Physical Row Order Decides What an Index Scan Costs A Pool Reuses Connections, and Exhaustion Presents as a Slow Database Rather Than an Error work_mem and Disk Spills: Reading Sort Method external merge in a Plan SQL JOIN Indexing Strategies for Efficient Lookup B-Tree Depth and Fan-Out: Why an Index on 100 Million Rows Is Four Levels Deep Implicit Type Casting: When the Engine Converts One Side of Your Comparison Cardinality Estimation Error EXPLAIN ANALYZE An Index Only Scan Needs the Visibility Map to Skip the Heap Query Execution Plan What a Prepared Statement's Parameter Is, and Why the Engine Cannot See Its Value The Average Response Time Describes Nobody; the Tail Describes Your Angriest Users InnoDB Clustered Primary Key and What a Secondary Index Lookup Costs in MySQL HashAggregate vs GroupAggregate: The Two Ways a Database Does GROUP BY Database Buffer Pool Recursive Subqueries in SQL Data Analysis A Query That Is Slow Only Under Load Is Usually Waiting, Not Working Let the Database Do the Arithmetic Instead of Looping in Your Code Follow the Blocking Chain With pg_locks and the Blocking PIDs Function Foreign Keys and Primary Keys Relational Database Design Raise the Statistics Target on a Skewed Column