PostgreSQL Internals

No Picture
PostgreSQL Performance

PostgreSQL Statistics: How to Gather Them 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 […]
No Picture
PostgreSQL Internals

PostgreSQL SQL Plans: Why They 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 […]
No Picture
PostgreSQL Internals

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 […]
No Picture
PostgreSQL Internals

How to have PostgreSQL execution plan with run-time?

PostgreSQL execution plan with run-time 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 […]
No Picture
PostgreSQL Performance

Adaptive Joins PostgreSQL: Improving Query Performance

Introduction: Adaptive joins are an exciting feature in PostgreSQL. They dynamically adjust the join method during query execution to optimize performance. This article explores adaptive joins in PostgreSQL, their benefits, and how they can improve […]
No Picture
PostgreSQL Internals

PostgreSQL Aggregation and Spools: Implementing Plans

In PostgreSQL, plans are implemented by using the query execution plan generated by the query optimizer. Aggregation and spool operations serve as key components in query plans, enabling efficient processing of aggregate queries. Let’s now […]