Postgre database Insert, query performance optimization detailed _postgresql

First, the preface
The previous system because the table design is more complex (many tables, tables and tables directly with the master-slave relationship), this is the business logic decision. Insertion efficiency is simply unbearable and must be optimized. After understanding the copy,unlogged table characteristics of Postgre
After that, decide what to do.

Second, test cases

1. Datasheet structure: A table representing an employee's work performance (Work_test): A total of 15 fields
Id,no,name,sex,tel,address,provice,city,post,mobile,department,work,start_time,end_time,score
Index (clustered index of b-tree or called clustered index): id,no,name,sex,tel,address,provice,city,post,mobile,department,work

2. Test environment: Win7, Quad core, 2GB memory, postgre version 9.3;npgsql connection Postgre database.

Third, insert/transaction/copy/unlogged table

1.insert a 10W data probably need 120s, although has promoted "many", but still not satisfactory. Previously used SQLite found transaction can significantly improve performance, so in the Postgre to try, found no significant changes
Of. Somehow.

2. Copy can copy data from a file (CSV) into a database, and of course the structure of the datasheet and the type of data you want to correspond to file one by one. Insert performance is said to be significantly improved.

Copy Code code as follows:

COPY ' work_test ' from ' c:\temp.csv ' with DELIMITER as ', '

　　
When you use copy, you find that the performance of the insert immediately increases to 30s, which is equivalent to 1s inserting 3,300 records. This also contains the time that the CSV file was generated.

3.unlogged table
unlogged table, the online article says it can be 10 times times the insert. The use method is also very simple: Create unlogged table ...
However, unlogged table loses all data when it encounters a Postgre server exception reboot, so if your data is not allowed to be lost, please do not use it.
After use, it is found that approximately 20s,1s inserts 5,000 records.

Here are three ways to insert the chart:

Description: The x axis represents the number of records already in the database, in million units, each point is the 10W.Y axis that represents the time spent per insertion, in seconds.
Blue Line: Insert; The reason behind the stability is that the computer does not run other programs. Therefore, the computer working condition has certain influence to the Postgre efficiency.
Pink: Copy;
Yellow Line: copy+unlogged
Although the index was built, and the data in the table has been added, there is no effect on the subsequent insertion of data performance, which is what I want to see.

Iv. Query Test
Search by Name field:

Copy Code code as follows:

SELECT * from Work_test where name= ' 1 '

Description: The x-axis represents the number of records already in the database, in million units, each point is the 10W.Y axis that represents the time spent per query, in milliseconds.

Blue Line: Insert;
Pink: Copy;
Yellow Line: copy+unlogged

Because the results of the three inserts are the same, there is no point in comparison, which is mainly about the time consuming of queries. Average down: 500ms, and as row count increases, query efficiency does not decrease. This is mainly thanks to good indexes.
Also found: the more conditions, the higher the query efficiency, because the number of scanned lines in the reduction, the following figure is not posted.