PostgreSQL performance optimization (10)

Overview of database optimization

Benchmarking

1. Quickly generate test data

2. Usage of pgbench

3. IO pressure measurement

4. Usage of sysbench

5. Netperf network card performance test

Linux monitoring tools

1. Linux monitoring tool (top, free,iostat,vmstat,sar)

    2.iotop

    3.pg_top

    4.iftop

Operating system optimization

    1.NUMA

    2.hugepage

3. VM parameter

4. io scheduling algorithm

5. Disk read ahead

6. Resource restrictions

7. Linux control group cgroup

Database optimization

    1.max_connections

    2.shared_buffers

    3.work_mem

    4.autovacuum

    5.dynamic_shared_memory_type

    6.checkpoint_timeout

    7.JIT

8. Tool tuning

Detailed Statistical Information

1. Statistics

2. Cost calculation

3. Monitoring statistics

4. Extended statistics

Detailed explanation of implementation plan

1. Overview of optimizer

2. View the execution plan

3. Control node

4. Physicochemical node

5. Scan nodes

6. Connection node

7. Usage of hint

8. Usage of auto_explain

9. Plan cache usage

10. Multi meter connection optimization

11. Implementation plan optimization

Parallel Query Details

1. Introduction to Parallel Query Principle

2. Parallel query related parameters

3. Application case of parallel computing

Discover TOP SQL

1. Find top sql

2. Installation and use of pg_profile

3. pg_corn scheduled task

dba

1. Basic idea of SQL optimization

2. Simple index optimization

3. Fuzzy query optimization

4. Multi filter condition optimization

5. Small part of data query optimization

6. Pagination query optimization

7. Optimization cases (17 optimization cases)

SQL Overwrite

1. Query conversion

2. Promote sub query

3. Relationship between tables

4. Scalar subquery

5. Semi join

6. Anti join

7. Filter cycle

8. Outer join

9. Rewrite cases (16 rewrite cases)