PostgreSQL database (indexes, views)

The meaning and characteristics of the index

An index is a separate database structure stored on disk that contains reference pointers to all records of the data, the PostgreSQL column type can be indexed, and the related column index is the best way to improve the efficiency of query operations. For example, query the select * from table where num=10000. If there is no index, the entire table must be traversed, and if an index is created on NUM, PostgreSQL does not need any scanning, and the direct index is 10000. You know where the line is.

The advantages of the index are: 1234 unique index, ensure data integrity 2 improve query data 3 to achieve the integrity of data reference, accelerate table and table connection 4. Improve the speed of data grouping and sorting operations.

Disadvantages are: 1 index building consumes a certain amount of physical space 2 creating an index takes time, increases the amount of data, and the time it takes to create an index increases by 3 data maintenance, indexes need to be maintained dynamically

Index design principles: 1. The index is not the more the better, a large number of indexes occupy a large amount of disk space, affecting the speed of INSERT, delete, update

2. Avoid indexing frequently updated tables, create indexes on frequently queried tables, and avoid having to add fields

3. A table with a small amount of data is best not indexed, the amount of data is low, and the query may take less time than traversing the index

4. Indexing on columns that are frequently used in conditional expressions, such as "gender fields", that do not need to be indexed

5. When uniqueness is a feature of the data itself, create a unique index that guarantees column integrity and improves query speed

6. When an index is established on a column that is frequently set up for sorting or grouping, you can create a composite index on those columns if you have more than one sorted column

View

View Benefits: 1 Simplification

2 security, you can use the database Authorization command to restrict each user's retrieval of a database to a specific database object. However, you can limit the user to a different subset of the data through a view, but not on specific rows and columns.

3 Ensuring the independence of logical data

PostgreSQL database (indexes, views)