Greenplum manage Indexes

Greenplum manages indexes. Considering the characteristics of OLAP systems, you should use indexes carefully and conservatively. Avoid using indexes on frequently updated columns; use B-tree indexes on highly selective columns; and use Bitmap indexes on low-selective columns. In general, using indexes in traditional databases can effectively improve data access efficiency. Especially in OLTP systems, you often only need to obtain several rows or parts of data records from large tables, in this case, the index is indeed a particularly effective method to increase the data acquisition speed. However, this may not be the case in gp, because the data is evenly distributed to segs to the largest extent possible, which means that each instances is only a part of the overall data scanning, and if the Partitioning technology is used, the number of data to be scanned may be less. What is different from OLTP is that most of the Record Sets are often obtained in the big data environment, in this case, using indexes may not be an effective way to increase the data acquisition speed. You should use indexes conservatively. The correct method should be to test the performance without creating any indexes, and then make the correct decision. If a primary key is used, an index is automatically created for it. If an index is used in a compressed read-only table, the index only extracts the required data instead of the entire table; avoid creating indexes on frequently updated columns. Use B-tree indexes on highly selective columns and bitmap indexes on low-selective columns. Create indexes without foreign keys or associated keys; it is unnecessary to create indexes for frequently used filter words and avoid duplicate indexes on the same column. indexes are deleted before data is loaded, this is usually faster than the update index; use a technology similar to the cluster Index to create an index. B-tree, GiST, and Bitmap indexes do not support hash and gin indexes. B-tree indexes are created by default; the unique index must be included in the distribution key (it can be all or some columns, some columns can be included in the 1st index, and all columns must be later). The unique index does not support the ao table, the unique index does not take effect across partitions, but only for independent partitions. Bitmap indexes occupy less space than normal indexes, and can play a very good effect between 100 and 100000 different key values, bitmap indexes should not be used in frequently updated tables. Create index idx1 on t3 (id); create index idx2 on t3 using bitmap (id); drop index idx1; Use the explain command to check whether indexes are used: index scan, bitmap heap scan, bitmap index scan, BitmapAnd or BitmapOr re-build the index reindex idx1. The re-indexing will use the data information on the existing index, this is much faster than deleting and recreating indexes. The Delete and update operations do not update bitmap indexes. Therefore, you must manually reindex bitmap indexes. Gtlions = # select * from pg_indexes where tablename = 't1 '; schemaname | tablename | indexname | tablespace | indexdef ------------ + ----------- + ------------- + USING gtlions | t1 | t1_pkey | primary | create unique index t1_pkey ON t1 USING btree (id) (1 row)-EOF-