×
Community Blog PostgreSQL-Based Automatic Performance Diagnosis and Optimization

PostgreSQL-Based Automatic Performance Diagnosis and Optimization

This short article discusses Postgres Query Analysis 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
0 0 0
Share on

digoal

232 posts | 16 followers

You may also like

Comments