Query Performance

Clustered vs Non-Clustered Index: What is the Difference?

A clustered index determines the physical order in which table rows are stored on disk, while a non-clustered index is a separate structure that stores pointer…

How Does a B-Tree Index Work Internally?

A B-tree index is a balanced, sorted tree of fixed-size nodes where every leaf sits at the same depth, letting the database find any key in O(log n) disk reads…

Hash Index vs B-Tree Index: What is the Difference?

A hash index maps each key to a bucket via a hash function, giving O(1) average-case exact-match lookups but no support for range queries or ordering, while a…

What is a Covering Index in SQL?

A covering index is an index that includes every column a query needs — both the filter/sort columns and the selected columns — so the database engine can answ…

What is a Partial Index and When Should You Use One?

A partial index is an index built over only the subset of rows that satisfy a WHERE condition, rather than every row in the table, which keeps it smaller, fast…

What Are Index Selectivity and Cardinality?

Cardinality is the number of distinct values in a column, and selectivity is the ratio of distinct values to total rows — a high-selectivity column (close to 1…

How Does the Hash Join Algorithm Work?

A hash join builds an in-memory hash table from the smaller input table keyed on the join column, then probes that table with each row of the larger input, mat…

How Does the Merge Join (Sort-Merge Join) Algorithm Work?

A merge join, also called a sort-merge join, works by taking two inputs that are already sorted on the join key (or sorting them first), then walking both sort…

What is Query Plan Caching in a Database?

Query plan caching is the database engine's practice of storing a compiled execution plan for a query the first time it is optimized, then reusing that same pl…

What is the Parameter Sniffing Problem in SQL?

Parameter sniffing is the situation where a database compiles and caches a query plan optimized for the specific parameter values passed on the first execution…

What Role Do Statistics and Histograms Play in Query Planning?

Statistics and histograms are metadata the database keeps about the distribution of values in each column, and the query optimizer uses them to estimate how ma…

What are Window Functions in SQL?

A window function computes a value across a set of related rows (its window) for each row individually, without collapsing those rows into a single output row…

CTE vs Subquery: Which Performs Better?

Neither a CTE nor a subquery is inherently faster; on most modern optimizers, including PostgreSQL 12+ and SQL Server, a non-recursive CTE is inlined into the…

UNION vs UNION ALL: What is the Difference?

UNION combines the result sets of two or more SELECT queries and removes duplicate rows, while UNION ALL combines them and keeps every row, including duplicate…

How Do You Choose a Sharding Key?

A good sharding key is a column with high cardinality and an even, predictable value distribution that matches your query patterns, so writes and reads spread…

What is Range-Based Sharding?

Range-based sharding assigns rows to shards based on contiguous ranges of the shard key's value, so each shard owns a specific, ordered slice such as user IDs…

How Do Prepared Statements Improve Database Performance?

A prepared statement is parsed, validated, and compiled into an execution plan once, and then executed repeatedly with different parameter values, so the datab…

What Are Parameterized Queries and Why Do They Matter?

A parameterized query is a SQL statement written with placeholders in place of literal values, where the actual values are supplied separately and bound by the…

Stored Procedures vs ORM: What Are the Trade-offs?

Stored procedures push logic and multi-statement operations into the database itself for performance and centralized control, while an ORM keeps logic in appli…

Columnar Storage vs Row Storage: What is the Difference?

Row storage writes all columns of a single record contiguously on disk, making it fast to read or write an entire row at once, while columnar storage groups ea…

What Are TimescaleDB Hypertables and How Do They Work?

A TimescaleDB hypertable is a PostgreSQL table that is automatically partitioned behind the scenes into many smaller physical tables called chunks, typically s…

What Is Downsampling in Time-Series Databases?

Downsampling is the process of reducing high-resolution time-series data into lower-resolution aggregates (like per-minute or per-hour averages) after it ages…

How Do You Detect a Query Performance Regression?

You detect a query performance regression by continuously capturing query execution plans and timing statistics, comparing them against a historical baseline,…

How Does Run-Length Encoding Work in Columnar Compression?

Run-length encoding (RLE) compresses a column by replacing consecutive repeated values with a single stored value plus a count of how many times it repeats in…