PostgreSQL-Based Automatic Performance Diagnosis and Optimization
By digoal
Background
We recommend checking the official documents first for analysis and suggestions on various bottlenecks.
Postgres Query Analysis and Postgres Plan Explanation
Discover the root cause of critical issues, optimize slow queries, and find missing indices
Use the results of Explain Analyze to analyze SQL performance problems and optimization methods. Explain Analyze has open-source components and the Enterprise Edition of OpenAPI. It is a good product with a performance diagnosis feature.
If you are an experienced DBA, you can develop your product in a similar way. You can implement API-based products and apply the membership system for products on a per-call basis.
SQL review product, SQL optimization product, monitoring product, etc.:
Documentation
Installation Guide
Troubleshooting
Log Insights
Setup
Tuning Log Config Settings
Collect Postgres EXPLAIN plans using auto_explain
Classifications
Server
Connections
WAL & Checkpoints
Autovacuum
Locks
Statements
Standby Servers
Constraint Violations
Application / User Errors
EXPLAIN
Insights
Disk Sort
Expensive
Hash Batches
Inefficient Index
I/O Heavy
Large Offset
Lossy Bitmaps
Mis-Estimate
Slow Scan
Stale Stats
Scan nodes
Sequential Scan
Index Scan
Index-Only Scan
Bitmap Heap Scan
Bitmap Index Scan
CTE Scan
Custom Scan
Foreign Scan
Function Scan
Subquery Scan
Table Sample Scan
Tid Scan
Values Scan
Work Table Scan
Join nodes
Hash Join
Merge Join
Nested Loop
Other nodes
Aggregate
Append
Bitmap And
Bitmap Or
Gather Merge
Gather
Group
Hash
Limit
Lock Rows
Materialize
Merge Append
Modify Table
Project Set
Recursive Union
Result
SetOp
Sort
Unique
Window Aggregate
Enterprise Edition
Initial Setup
Release Changelog
Log Insights Setup
Google Auth Setup
Upgrading to new releases
pganalyze GraphQL API
Creating an API key
Queries
getIssues - Get check-up issues and alerts
getQueryStats - Export query statistics
Mutations
addServer - Add a server to pganalyze Enterprise Edition
Permissions and Roles
Open-Source Components
Read previous post:
PostgreSQL plpgsql Debug - Black Screen and Text Mode Storage Procedure Debugging
Read next post:
Comments
Related Products
-
PolarDB for PostgreSQL
Alibaba Cloud PolarDB for PostgreSQL is an in-house relational database service 100% compatible with PostgreSQL and highly compatible with the Oracle syntax.
Learn More -
Database for FinTech Solution
Leverage cloud-native database solutions dedicated for FinTech.
Learn More -
Oracle Database Migration Solution
Migrate your legacy Oracle databases to Alibaba Cloud to save on long-term costs and take advantage of improved scalability, reliability, robust security, high performance, and cloud-native features.
Learn More -
Database Migration Solution
Migrating to fully managed cloud databases brings a host of benefits including scalability, reliability, and cost efficiency.
Learn More
More Posts by digoal
- Tips for Executing SQL Statements in Batch: Which Is Better, gexec or Dynamic SQL?
- What Could Be the Factor Leading to the Slow Execution of the First SQL Statement?
- How to Remove Duplicate and Similar Contents in Vector Databases
- How to Implement Parallelism and Concurrency Control (Queue) in Shell
- How to Determine Whether the Current PostgreSQL Database is in a Consistent State?
- PolarDB for PostgreSQL | Retrieval and Matching-Degree Sorting by Tags on Blogs and Websites
- Three Key Details and Principles of PostgreSQL Online Backup & Recovery
- PolarDB for PostgreSQL | Database Performance Optimization for High-Concurrency Queue Processing
- Implementing PostgreSQL Hook: Stats on Tables with Full Scans & Corresponding SQLs
- Optimization on Memory Usage During Rust Cargo Code Compiling