Articles by Shiv Iyer

About Shiv Iyer

Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.

Streaming versus blocking operators in MySQL

In MySQL, operators in a query execution plan can be classified as streaming or blocking. This classification is based on how they process and propagate rows during query execution. Let’s explore the differences between streaming […]

Tips and Tricks – Troubleshooting Redo Performance in InnoDB.

Troubleshooting redo performance in InnoDB involves identifying and addressing potential bottlenecks or issues that affect the efficiency of redo log processing. The redo log plays a critical role in ensuring data durability and crash recovery […]

MySQL Scalar Subquery Unnesting transformation

Scalar subquery unnesting is a transformation technique that the MySQL optimizer uses to optimize queries involving scalar subqueries. Scalar subqueries return a single value. You typically use them in expressions or as part of the […]

How to control MySQL execution plans with hints?

In MySQL, you can use query hints to give suggestions or directives to the query optimizer. These hints guide the optimizer on how to generate the execution plan for a specific query. As a result, […]

How to gather statistics of PostgreSQL only when stale?

In PostgreSQL, you can gather statistics only when they become stale by using the pg_stat_statements extension along with the track_activity_query_size configuration parameter. Follow these steps to achieve this: CREATE EXTENSION pg_stat_statements; 3. Query the pg_stat_statements […]

Why do I have SQL statement plans in PostgreSQL that change for the worse?

In PostgreSQL, several reasons can cause SQL statement plans to worsen over time. These changes may lead to decreased query performance and unexpected behavior. Below are some common factors that contribute to deteriorating SQL statement […]

How to implement Concurrent Statistics Gathering in PostgreSQL?

Concurrent statistics gathering in PostgreSQL allows the system to collect table and column statistics without blocking concurrent read and write operations. As a result, this feature ensures accurate and up-to-date statistics while minimizing the impact […]

How do I force a query to use bind-aware cursor sharing in PostgreSQL?

In PostgreSQL, you can force a query to use bind-aware cursor sharing by utilizing the CURSOR_SHARING parameter. By setting CURSOR_SHARING to ‘FORCE’, PostgreSQL will attempt to share cursors for similar queries with different literal values. […]

PostgreSQL Query Optimizer: Troubleshooting Unconsidered Hints

PostgreSQL’s query optimizer (or planner) creates optimal execution plans for SQL queries by evaluating multiple possible plans and selecting the fastest one. [^1] Understanding PostgreSQL’s Approach to Optimizer Hints PostgreSQL takes a deliberately cautious approach […]

How to have PostgreSQL execution plan with run-time?

PostgreSQL Execution Plan In PostgreSQL, you can obtain the execution plan with runtime information using the EXPLAIN (ANALYZE, VERBOSE) command. This command in particular, provides a detailed execution plan along with actual runtime information, including […]