Detailed description of Postgre database Insert and Query performance optimization

I. Preface
In the past, because the table design was complex (multiple tables have direct master-slave relationships with tables), this was determined by the business logic. The insertion efficiency is simply intolerable and must be optimized. After learning about the Copy and unlogged table features of Postgre
Then, we decided to find out.

Ii. Test Cases

1. Data Table Structure: A table indicating employee performance (work_test): 15 fields in total
Id, no, name, sex, tel, address, provice, city, post, mobile, department, work, start_time, end_time, score
Index (B-tree Cluster Index or clustered index): id, no, name, sex, tel, address, provice, city, post, mobile, department, work

2. test environment: win7, quad-core, 2 GB memory; postgre version 9.3; Npgsql connected to the Postgre database.

Iii. insert/transaction/copy/unlogged table

1. It takes about 120 s to insert a 10 W data. Although it has been improved by "a lot", it is still unsatisfactory. Previously, when SQLite was used, it was found that Transaction could greatly improve the performance, so I tried it in Postgre and found that it was not significantly changed.
. Somehow.

2. copy can copy the data in the file (csv) into the database. Of course, the structure and type of the data table must correspond to the file one by one. It is said that the insert performance can be greatly improved.
Copy codeThe Code is as follows:
COPY 'work _ test' from 'C: \ temp.csv 'with delimiter ','

　　
After using Copy, we found that the insert performance was immediately increased to 30 s, which is equivalent to inserting 3300 records in 1 s. This also contains the time when the csv file was generated.

3. unlogged table
Unlogged table. The online article says it can be 10 times better than insert. It's easy to use: Create unlogged table...
However, when the Postgre server restarts abnormally, The unlogged table will lose all the data. If your data cannot be lost, do not use it.
After use, we found that 5000 records were inserted in about 20 s and 1 s.

The following figure shows the trend of three insertion methods:

Note: The X axis indicates the number of existing records in the database, in the unit of millions. Each point is. the Y axis indicates the time consumed by each insert, in seconds.
Blue Line: insert; the reason why the system is relatively stable is that no other program is running on the computer. Therefore, the computer's working status has a certain impact on Postgre efficiency.
Pink: copy;
Yellow Line: copy + unlogged
Although the index has been created and the data in the table has been accumulated, there is no impact on the performance of subsequent data insertion. This result is what I want to see.

Iv. query Test
Search by name field:
Copy codeThe Code is as follows:
Select * from work_test where name = '1'




Note: The X axis indicates the number of existing records in the database, in the unit of millions. Each point is. the Y axis indicates the time consumed by each query, in milliseconds.

Blue Line: insert;
Pink: copy;
Yellow Line: copy + unlogged

Because the results of the three insert methods are the same, the comparison does not make sense. Here we mainly look at the query time. Average: 500 ms, and the query efficiency does not decrease as the Row count increases. This mainly benefits from good indexes.
In addition, the more conditions, the higher the query efficiency. because the number of scanned rows is decreasing, the following figure will not be posted.