On the index of PostgreSQL

1. Characteristics of the index 1.1 accelerating the retrieval of conditions

The query slows down as the amount of table data becomes larger, uses the index on the table's criteria fields, quickly navigates to records that might satisfy the criteria, and does not need to traverse all records.

 
create table t(id int, info text);
insert into t select generate_series(1,10000),'lottu'||generate_series(1,10000);
create table t1 as select * from t;
create table t2 as select * from t;
create index ind_t2_id on t2(id);

Lottu=# analyze t1;
ANALYZE
Lottu=# analyze t2;
ANALYZE
# No index
Lottu=# explain (analyze,buffers,verbose) select * from t1 where id < 10;
                                             QUERY PLAN
-------------------------------------------------- -------------------------------------------------- -
 Seq Scan on lottu.t1 (cost=0.00..180.00 rows=9 width=13) (actual time=0.073..5.650 rows=9 loops=1)
   Output: id, info
   Filter: (t1.id < 10)
   Rows Removed by Filter: 9991
   Buffers: shared hit=55
 Planning time: 25.904 ms
 Execution time: 5.741 ms
(7 rows)
Indexed
Lottu=# explain (analyze,verbose,buffers) select * from t2 where id < 10;
                                                     QUERY PLAN
-------------------------------------------------- -------------------------------------------------- -----------------
 Index Scan using ind_t2_id on lottu.t2 (cost=0.29..8.44 rows=9 width=13) (actual time=0.008..0.014 rows=9 loops=1)
   Output: id, info
   Index Cond: (t2.id < 10)
   Buffers: shared hit=3
 Planning time: 0.400 ms
 Execution time: 0.052 ms
(6 rows)

#In this case: Executes the same SQL. T2 indexed execution data is 0.052 ms;t1 no index is: 5.741 MS;

1.2 Ordered characteristics

The index itself is orderly.

#没有索引
Lottu=# explain (analyze,verbose,buffers) select * from t1 where id > 2 order by id;
                                                   QUERY PLAN
-------------------------------------------------- -------------------------------------------------- -------------
Sort (cost=844.31..869.31 rows=9999 width=13) (actual time=8.737..11.995 rows=9998 loops=1)
   Output: id, info
   Sort Key: t1.id
   Sort Method: quicksort Memory: 853kB
   Buffers: shared hit=55
   -> Seq Scan on lottu.t1 (cost=0.00..180.00 rows=9999 width=13) (actual time=0.038..5.133 rows=9998 loops=1)
         Output: id, info
         Filter: (t1.id > 2)
         Rows Removed by Filter: 2
         Buffers: shared hit=55
 Planning time: 0.116 ms
 Execution time: 15.205 ms
(12 rows)
 #有索引
Lottu=# explain (analyze,verbose,buffers) select * from t2 where id > 2 order by id;
                                                         QUERY PLAN
-------------------------------------------------- -------------------------------------------------- -------------------------
 Index Scan using ind_t2_id on lottu.t2 (cost=0.29..353.27 rows=9999 width=13) (actual time=0.030..5.304 rows=9998 loops=1)
   Output: id, info
   Index Cond: (t2.id > 2)
   Buffers: shared hit=84
 Planning time: 0.295 ms
 Execution time: 7.027 ms
(6 rows)

#In this case: Executes the same SQL.

• T2 indexed execution data is 7.027 MS;T1 no index is: 15.205 MS;
• T1 no index execution also takes up memory:853kb.

2. Index Scan mode

There are 3 ways to scan an index

2.1 Indexscan

Check the index to find the matching records of the Ctid, and then through the Ctid Check heap table

2.2 Bitmapscan

Check the index to find the matching records of the Ctid collection, the Ctid through bitmap do set operation and sorting and then check the heap table

2.3 Indexonlyscan

If the index field contains all the returned fields, the data blocks in the visibility map (VM) are all visible, and the heap tables are not returned directly to the values in the index.

Here we talk about Indexscan scanning mode and Indexonlyscan scanning mode
The difference between these two methods of scanning, borrowed from the Oracle index scanning mode, Indexscan scanning mode will produce back to the table read. According to the above explanation, Indexscan scanning method: After checking the index also need to look up the table. The Indexonlyscan scanning method only needs to check the index. In other words: Indexonlyscan scanning mode is better than Indexscan scan mode? Let's take a look.

Existing table t; built ind_t_id index on the field id
1. The t table does not have a VM file.
Lottu=# \d+ t
                           Table "lottu.t"
 Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+----------+-------- ------+-------------
 Id | integer | | plain |
 Info | text | | extended | |
Indexes:
    "ind_t_id" btree (id)

Lottu=# explain (analyze,buffers,verbose) select id from t where id < 10;
                                                      QUERY PLAN
-------------------------------------------------- -------------------------------------------------- -------------------
 Index Only Scan using ind_t_id on lottu.t (cost=0.29..8.44 rows=9 width=4) (actual time=0.009..0.015 rows=9 loops=1)
   Output: id
   Index Cond: (t.id < 10)
   Heap Fetches: 9
   Buffers: shared hit=3
 Planning time: 0.177 ms
 Execution time: 0.050 ms
(7 rows)
#人为改实施计划
Lottu=# set enable_indexonlyscan = off;
SET
Lottu=# explain (analyze,buffers,verbose) select id from t where id < 10;
                                                    QUERY PLAN
-------------------------------------------------- -------------------------------------------------- --------------
 Index Scan using ind_t_id on lottu.t (cost=0.29..8.44 rows=9 width=4) (actual time=0.008..0.014 rows=9 loops=1)
   Output: id
   Index Cond: (t.id < 10)
   Buffers: shared hit=3
 Planning time: 0.188 ms
 Execution time: 0.050 ms
(6 rows)
# You can see that there is almost no difference between the two; the only difference is that the Indexonlyscan scan method has the Heap Fetches time of the scan. This time is not in the Execution time.
2. t table has VM file
Lottu=# delete from t where id >200 and id < 500;
DELETE 299
Lottu=# vacuum t;
VACUUM
Lottu=# analyze t;
ANALYZE
Lottu=# explain (analyze,buffers,verbose) select id from t where id < 10;
                                                      QUERY PLAN
-------------------------------------------------- -------------------------------------------------- -------------------
 Index Only Scan using ind_t_id on lottu.t (cost=0.29..4.44 rows=9 width=4) (actual time=0.008..0.012 rows=9 loops=1)
   Output: id
   Index Cond: (t.id < 10)
   Heap Fetches: 0
   Buffers: shared hit=3
 Planning time: 0.174 ms
 Execution time: 0.048 ms
(7 rows)

Lottu=# set enable_indexonlyscan = off;
SET
Lottu=# explain (analyze,buffers,verbose) select id from t where id < 10;
                                                    QUERY PLAN
-------------------------------------------------- -------------------------------------------------- --------------
 Index Scan using ind_t_id on lottu.t (cost=0.29..8.44 rows=9 width=4) (actual time=0.012..0.022 rows=9 loops=1)
   Output: id
   Index Cond: (t.id < 10)
   Buffers: shared hit=3
 Planning time: 0.179 ms
 Execution time: 0.077 ms
(6 rows)

Summarize:

• Index only scan is slower than index scan in the absence of a VM file because all the heap page is scanned. The difference is almost small.
• Index only scan has a VM file that is faster than the index scan.

Knowledge point 1:

• VM file: Known as a visibility mapping file, the file exists indicating that the data block has no rows to clean up. That has already done the vaccum operation.

Knowledge Point 2:

An artificial selection of execution plans. can be set enable_xxx parameters have

• Enable_bitmapscan
• Enable_hashagg
• Enable_hashjoin
• Enable_indexonlyscan
• Enable_indexscan
• Enable_material
• Enable_mergejoin
• Enable_nestloop
• Enable_seqscan
• Enable_sort
• Enable_tidscan

Reference documents

• Reference with: "PostgreSQL performance Optimization training 3 day.pdf"
• Https://www.postgresql.org/docs/9.6/static/runtime-config-query.html

3. Types of indexes

PostgreSQL Support index types are: B-tree, Hash, GiST, Sp-gist, GIN and BRIN.

• PostgreSQL----Btree Index: http://www.cnblogs.com/alianbog/p/5621749.html
• PostgreSQL----Hash index: Typically used only for simple equivalence queries. Not used.
• PostgreSQL----GIST Index: http://www.cnblogs.com/alianbog/p/5628543.html

4. Index management 4.1 Creating an Index

To create an index syntax:

Lottu=# \h create index
Command: CREATE INDEX
Description: define a new index
Syntax:
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]
Next we take the t table as an example.
1. Keyword [UNIQUE]
#Create a unique index; the primary key is a unique index
CREATE UNIQUE INDEX ind_t_id_1 on t (id);
2. Keyword [CONCURRENTLY]
# This is an index created concurrently. The same as the oracle online index creation. During the index creation process; table update, insert, delete operations are not blocked. Of course, the time of creation will be very long.
CREATE INDEX CONCURRENTLY ind_t_id_2 on t (id);
3. Keyword [IF NOT EXISTS]
#Use this command to confirm the existence of the index name. If it exists, it will not give an error.
CREATE INDEX IF NOT EXISTS ind_t_id_3 on t (id);
4. Keyword [USING]
#Create which type of index. The default is B-tree.
CREATE INDEX ind_t_id_4 on t using btree (id);
5 Keywords [[ ASC | DESC ] [ NULLS { FIRST | LAST]
# Create an index in descending or ascending order. If the field has a null value, the null value is placed first or last: for example, in descending order, null is placed first.
CREATE INDEX ind_t_id_5 on t (id desc nulls first)
6. Keyword [WITH ( storage_parameter = value)]
#Index's fill factor is set to . For example, the fill factor for creating an index is set to 75.
CREATE INDEX ind_t_id_6 on t (id) with (fillfactor = 75);
7. Keyword [TABLESPACE]
# is the table space in which the index is created.
CREATE INDEX ind_t_id_7 on t (id) TABLESPACE tsp_lottu;
8. Keywords [WHERE]
#Create an index only on the part of the data that you are interested in, instead of creating an index on each row of data. In this way, you need to use the WHERE condition to create an index.
CREATE INDEX ind_t_id_8 on t (id) WHERE id < 1000;

4.2 Modifying indexes

Modify Index syntax

Lottu=# \h alter index
Command: ALTER INDEX
Description: change the definition of an index
Syntax:
#Rename the index
ALTER INDEX [ IF EXISTS ] name RENAME TO new_name
#Index index tablespace
ALTER INDEX [ IF EXISTS ] name SET TABLESPACE tablespace_name
#重Index reset fill factor
ALTER INDEX [ IF EXISTS ] name SET ( storage_parameter = value [, ... ] )
#Set the fill factor of the index to the default value
ALTER INDEX [ IF EXISTS ] name RESET ( storage_parameter [, ... ] )
#Migrate the index in table space TSP1 to the new table space
ALTER INDEX ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
     SET TABLESPACE new_tablespace [ NOWAIT ]

4.3 Deleting an index

Delete index syntax

lottu=# \h drop index
Command:     DROP INDEX
Description: remove an index
Syntax:
DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

5. Maintenance of the Index

Indexes can result in faster querying, sorting, and the role of unique constraints on records in a table. Indexes also have a price

• Indexes need to increase the storage space of the database.
• Perform INSERT, UPDATE, delete operations on table records. The index also needs to be updated.

5.1 Viewing the size of an index

Select Pg_size_pretty (pg_relation_size (' ind_t_id '));

5.2 Utilization of indexes

-- Check the number of times the index is scanned by pg_stat_user_indexes.idx_scan; this will confirm that those indexes can be cleaned up.
Select idx_scan from pg_stat_user_indexes where indexrelname = 'ind_t_id';

5.3 Rebuilding of the Index

-- If a table is updated frequently, the index performance is not good; you need to rebuild the index.
Lottu=# select pg_size_pretty(pg_relation_size('ind_t_id_1'));
  Pg_size_pretty
----------------
  2200 kB
(1 row)

Lottu=# delete from t where id > 1000;
DELETE 99000

Lottu=# analyze t;
ANALYZE
Lottu=# select pg_size_pretty(pg_relation_size('ind_t_id_1'));
  Pg_size_pretty
----------------
  2200 kB
 
Lottu=# insert into t select generate_series(2000,100000),'lottu';
INSERT 0 98001

Lottu=# select pg_size_pretty(pg_relation_size('ind_t_id_1'));
  Pg_size_pretty
----------------
  4336 kB
(1 row)

Lottu=# vacuum full t;
VACUUM

Lottu=# select pg_size_pretty(pg_relation_size('ind_t_id_1'));
  Pg_size_pretty
----------------
  2176 kB
 
Reconstruction method:
1. reindex: reindex does not support parallel reconstruction [CONCURRENTLY]; index will lock the table; will block.
2. vacuum full; Refactor the table; the index will also be rebuilt; the table will also be locked.
3. Create a new index (with different index names); then delete the old index. 

(turn) on the index of PostgreSQL