Tag: #query-optimization
Writing on GPUs, LLMs, MLOps, Kubernetes — and mindset · 13 posts
The Complete Guide to SQL Execution Plans: How the Optimizer Chooses a Plan
This piece explains the execution plan from the point of view of the PostgreSQL 18 planner: how statistics are collected, how selectivity is calculated, which constants assemble the cost numbers, when join-order search h
2026-08-15 · 24 min read #database#postgresql#execution-plan#query-optimization#optimizerWhy the Index You Created Is Not Being Used — The Cases Where the Optimizer Is Right and You Are Wrong
A cause-by-cause breakdown of why a Seq Scan stays in the query plan even after you create an index. Low selectivity making the optimizer ignore it on purpose, a function or arithmetic on the column making the condition
2026-07-26 · 14 min read #database#postgresql#index#query-optimization#mysqlHow to Read EXPLAIN ANALYZE — Finding the Real Bottleneck in a Query Plan
A walkthrough of how to read EXPLAIN ANALYZE output from beginning to end. Which order to read the nodes in, why cost is not a unit of time, what the gap between estimated and actual row counts tells you, and how to avoi
2026-07-26 · 13 min read #database#postgresql#explain#query-optimization#performanceClickHouse Lazy Materialization — How a LIMIT 10 Trick Grew Into FINAL and JOIN
ClickHouse's lazy materialization is an optimization that skips reading SELECT columns until sorting and LIMIT are finished, and it launched in 25.4 (April 2025) as a conservative feature that only kicked in for LIMIT 10
2026-07-17 · 15 min read #database#clickhouse#olap#query-optimization#performancepg_plan_advice in Postgres 19: The Compromise From a Project That Has Long Refused Hints
The PostgreSQL project has refused optimizer hints for a long time. But PostgreSQL 19 Beta 1, released on June 4, 2026, added two contrib modules built by Robert Haas: pgplanadvice and pgstashadvice. This isn't Oracle-st
2026-07-16 · 14 min read #postgresql#query-planner#query-optimization#database#performancePolars 1.x vs Pandas — End of an Era? A Modern DataFrame Deep Dive for 2026
Polars hit 1.x with a stable-API promise, Pandas 2.2 finally adopted PyArrow as a real backend, and the gravity of the dataframe world shifted. Is Polars actually replacing Pandas? This deep dive walks through the Rust p
2026-05-14 · 20 min read #polars#pandas#dataframe#apache-arrow#duckdbPostgreSQL Internals Deep Dive — MVCC, VACUUM, WAL, Query Planner, Index, Partitioning, pgvector (2025)
Why did PostgreSQL become "the second most powerful DB after Oracle"? MVCC tuple versioning and the VACUUM burden, WAL and streaming replication, how the Query Planner builds execution plans, choosing among B-Tree/GIN/BR
2026-04-15 · 14 min read #postgresql#database#mvcc#query-optimization#infraDB Index Master Guide 2025: B-Tree, Hash, GIN, BRIN, Composite Indexes, Query Plan Analysis
Everything about DB indexes. B-Tree internals, comparison of Hash/GIN/GiST/BRIN, composite index design, covering/partial/expression indexes, reading EXPLAIN ANALYZE, 10 reasons an index is skipped, real-world tuning cas
2026-04-15 · 10 min read #database#indexing#btree#hash#ginDatabase Performance Tuning Complete Guide: Query Optimization, Index Strategy, Connection Pool Management
Everything about DB performance tuning! EXPLAIN ANALYZE interpretation, index design (B-Tree/GIN/BRIN), slow query analysis, connection pooling (HikariCP/PgBouncer), N+1 resolution, partitioning, read replicas, query cac
2026-03-24 · 22 min read #database#performance#tuning#indexing#query-optimizationSQL Mastery 2025: Developer Interview SQL — From JOINs to Window Functions and Query Optimization
Everything about SQL interviews! 7 types of JOINs, Window Functions (ROWNUMBER/RANK/LAG/LEAD), Subquery vs CTE, Index internals (B-Tree/Hash/GIN), EXPLAIN ANALYZE, Transaction Isolation Levels, and 30 practice problems.
2026-03-23 · 30 min read #sql#database#join#window-function#indexingPostgreSQL Query Optimization and Performance Tuning Practical Guide: From EXPLAIN to Partitioning
A systematic guide to practical performance optimization techniques for PostgreSQL including EXPLAIN analysis, index strategies, VACUUM tuning, partitioning, and connection pooling.
2026-03-14 · 19 min read #database#postgresql#query-optimization#performance#indexingMySQL Query Optimization Practical Guide: From EXPLAIN Analysis to Index Design and Slow Query Tuning
A comprehensive guide covering MySQL EXPLAIN/EXPLAIN ANALYZE output interpretation, B-Tree/Hash/Full-text index types, composite index design (leftmost prefix rule, covering index), query rewriting patterns (subquery to
2026-03-11 · 17 min read #database#mysql#query-optimization#explain#index-tuningPostgreSQL Partitioning Complete Guide: Range, List, Hash Strategies and Performance Optimization
Learn how to dramatically improve the performance of large tables using PostgreSQL declarative partitioning. Covers Range, List, and Hash partitioning strategies along with partition pruning and automated management.
2026-03-03 · 21 min read #database#postgresql#partitioning#performance#query-optimization