Several aspects of PostgreSQL database performance improvement _postgresql

1. Use of explain
The explain command can view the execution plan, as already described in the previous blog. This method is our most important debugging tool.

2. Update the statistics used in the execution plan in a timely manner
Since statistics are not updated every time the database is operated, it is usually updated in the VACUUM, ANALYZE, CREATE Index, and so on when the DDL is executed,
Therefore, the statistics used to execute the plan are likely to be older. The result of the analysis of the execution plan may be that the error will become larger.

The following is a section of statistics related to table TENK1.
SELECT Relname, Relkind, Reltuples, relpages
From Pg_class
WHERE relname like ' tenk1% ';

Relname | Relkind | Reltuples | Relpages
----------------------+---------+-----------+----------
Tenk1 | R | 10000 | 358
tenk1_hundred | I | 10000 | 30
tenk1_thous_tenthous | I | 10000 | 30
tenk1_unique1 | I | 10000 | 30
tenk1_unique2 | I | 10000 | 30
(5 rows)
Where Relkind is the type, r is its own table, I is the index index;reltuples is the number of items, Relpages is the number of blocks on the hard disk.

3. Explicitly use join to correlate tables
General wording: SELECT * from A, B, c WHERE a.id = b.id and b.ref = c.id;
If you explicitly use join, the execution plan is relatively easy to control.
Example:
SELECT * from a CROSS join B CROSS join C WHERE a.id = b.id and b.ref = c.id;
SELECT * from a JOIN (b join C in (B.ref = C.id)) on (a.id = b.id);

4. Turn off automatic submission (autocommit=false)

5. Inserting data multiple times is more efficient with the copy command
We have to do a lot of insert operations on the same table in all of our processing. This time we are more efficient with the copy command. Because inserts once, its related index all must do once, compares spends the time.

6. Temporary deletion of index
Sometimes when we back up and re-import data, it can take a few hours to complete if the data is very large. This time you can first delete the index. Import index under construction.

7. Deletion of foreign key associations
If the table has a foreign key, each operation does not go to check the foreign key integration. So slow. Setting up foreign keys after data import is also an option.

8. Increase the MAINTENANCE_WORK_MEM parameter size
Adding this parameter can enhance the execution efficiency of the CREATE INDEX and alter TABLE ADD FOREIGN key.

9. Increase the size of the checkpoint_segments parameter
Adding this parameter can increase the speed at which a large number of data is imported.

10. Invalid setting Archive_mode
When this parameter is set to invalid, you can increase the speed of the following operations
Create TABLE as SELECT
Create INDEX
Alter TABLE SET tablespace
cluster and so on.

11. Final implementation of Vacuum ANALYZE
The vacuum ANALYZE is recommended when the data in the table changes a lot.