Tagged articles
Query Planner
10 articles · Page 1 of 1
SQLite 3.46.0 introduces extensive PRAGMA enhancements, new date‑time modifiers, support for underscore digits in numeric literals, a json_pretty() function, significant query planner optimizations, improved JSON handling, and the ability to use large hexadecimal literals as column defaults, all while maintaining its public‑domain, self‑contained nature.
Database EngineJSONPRAGMA
0 likes · 4 min read

This article examines a MySQL deadlock case involving DELETE and UPDATE statements, explains why the primary key index is locked despite non‑key predicates, analyzes execution plans and optimizer trace, and proposes index restructuring and query hints to resolve the issue.
DeadlockICPMySQL
0 likes · 23 min read

This article explains how MySQL estimates the I/O and CPU costs of different indexes when executing COUNT(*) and other queries, demonstrates the calculations with a concrete example on a 100k‑row table, and shows why the optimizer sometimes picks a full‑table scan over a seemingly better index.
Database PerformanceEXPLAINMySQL
0 likes · 10 min read

The article explains how a Citus cluster—comprising a coordinator and multiple workers—splits and routes SQL queries, detailing the distributed planner, executor, push‑pull handling of subqueries/CTEs, and a step‑by‑step EXPLAIN walkthrough of a top‑20 page‑views query.
CitusDistributed QueryEXPLAIN
0 likes · 10 min read

SQLite version 3.38.0 introduces new JSON operators compatible with MySQL and PostgreSQL, built‑in JSON functions, added unixepoch() and auto/julianday modifiers for date/time, renamed printf() to format(), enhanced virtual table interfaces, CLI columnar output options, and query planner optimizations using Bloom filters and balanced merge trees.
Database EngineQuery PlannerSQLite
0 likes · 4 min read

Vitess 12, the latest major release of the MySQL clustering solution, introduces the experimental Gen4 query planner, enhanced VTAdmin multi‑cluster management, role‑based access control, updated benchmarking tools, and more inclusive naming conventions to improve scalability and cloud deployment.
MySQLQuery PlannerRBAC
0 likes · 5 min read

This article explains how SQL optimizers choose join methods and table order, compares nested loop and merge joins, outlines factors influencing table selection, and offers practical guidance for improving multi‑table query performance.
Database PerformanceMerge JoinNested Loop
0 likes · 8 min read


ITPUBJul 11, 2016 · Databases
This article explains PostgreSQL's kernel architecture, from client connection handling through parsing, rewriting, planning, and execution, illustrates scan and join algorithms, shows how to extend the kernel and build clustered deployments, and introduces Foreign Data Wrappers for integrating external data sources.
ClusterForeign Data WrapperIndex Scan
0 likes · 13 min read


ITPUBMay 31, 2016 · Databases
This article walks through PostgreSQL's complete SQL execution pipeline, detailing each component—from the Main entry point and Postmaster process to parsing, traffic coordination, query rewriting, plan generation, and execution—while comparing its process architecture to other database systems.
Database InternalsPostgreSQLPostmaster
0 likes · 6 min read

Although a users table has an index on the create_time column, a query spanning a broad date range triggers a full table scan, but narrowing the range enables the optimizer to use the index, illustrating how MySQL’s cost‑based optimizer decides between index lookup and table scan.
EXPLAINMySQLQuery Planner
0 likes · 2 min read
