PostgreSQL（三）索引&执行计划

索引：

-------------------------------
创建索引
-------------------------------
https://www.postgresql.org/docs/current/static/sql-createindex.html

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 ]

CONCURRENTLY :
创建索引默认情况是锁表只读，CONCURRENTLY 允许执行DML，但会花更多些时间。在线创建索引都会创建一个快照保留索引之前的数据，且创建索引也会加到CPU和IO的开销。创建过程如果死锁或唯一冲突导致失败，可能生成一个无效索引。

USING method ： btree(默认), hash, gist, spgist, gin, brin
可参考：PostgreSQL 9种索引的原理和应用场景（https://yq.aliyun.com/articles/111793）


检查索引是否无效（INVALID）：
postgres=# \d tab
       Table "public.tab"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 col    | integer |           |          | 
Indexes:
    "idx" btree (col) INVALID



--创建索引
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS ix_weather01 
ON weather 
USING btree (city COLLATE pg_catalog."default" varchar_pattern_ops ASC NULLS FIRST)
WITH (fillfactor = 90)
TABLESPACE pg_default
WHERE city is not null;

postgres=# \d weather

#查看索引
\di
\di ix_weather01;
\d+ ix_weather01;
SELECT * FROM pg_indexes WHERE tablename='weather';
SELECT * FROM pg_statio_all_indexes WHERE relname='weather';

#查看索引大小
SELECT pg_size_pretty(pg_relation_size('ix_weather01')); 

SELECT indexname,pg_size_pretty(pg_relation_size(cast(indexname as varchar))) as size 
FROM pg_indexes WHERE schemaname='public';

-------------------------------

#更改索引
ALTER INDEX [ IF EXISTS ] name RENAME TO new_name
ALTER INDEX [ IF EXISTS ] name SET TABLESPACE tablespace_name
ALTER INDEX name DEPENDS ON EXTENSION extension_name
ALTER INDEX [ IF EXISTS ] name SET ( storage_parameter = value [, ... ] )
ALTER INDEX [ IF EXISTS ] name RESET ( storage_parameter [, ... ] )
ALTER INDEX ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
    SET TABLESPACE new_tablespace [ NOWAIT ]
    
    
#更改索引属性
ALTER INDEX ix_weather01 RENAME TO ix_weather;
ALTER INDEX ix_weather SET TABLESPACE pg_default;
ALTER INDEX ix_weather SET (fillfactor = 80); 
ALTER INDEX ix_weather RESET (fillfactor); 
ALTER INDEX ALL IN TABLESPACE pg_default SET TABLESPACE ts_user01 NOWAIT;


#重建索引
REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } name
 
REINDEX INDEX ix_weather;
REINDEX TABLE weather;
REINDEX SCHEMA public;
REINDEX DATABASE testdb;
REINDEX SYSTEM hzc;


#删除索引
DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

DROP INDEX ix_weather;

执行计划：

#查看执行计划
# https://www.postgresql.org/docs/current/static/sql-explain.html

EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

option:
    ANALYZE [ boolean ] --显示实际执行实际和其他统计信息。默认：FALSE
    VERBOSE [ boolean ] --输出执行计划相关的额外信息。默认：FALSE
    COSTS [ boolean ]   --输出每个节点估计开销、行数、行宽。默认：TRUE
    BUFFERS [ boolean ] --缓存信息。块的命中、读写情况，ANALYZE 启用时才有用。默认：FALSE
    TIMING [ boolean ]  --实际的时间，ANALYZE 启用时才有用。默认：FALSE
    SUMMARY [ boolean ] --概要信息，如总时间等。
    FORMAT { TEXT | XML | JSON | YAML } --定义输出格式。默认：TEXT
   

# EXPLAIN SELECT * FROM weather WHERE city='San Francisco';
                       QUERY PLAN                        
---------------------------------------------------------
 Seq Scan on weather  (cost=0.00..1.04 rows=1 width=194)
   Filter: ((city)::text = 'San Francisco'::text)
(2 rows)
#
#
# EXPLAIN ANALYZE SELECT * FROM weather WHERE city='San Francisco';
                                            QUERY PLAN                                             
---------------------------------------------------------------------------------------------------
 Seq Scan on weather  (cost=0.00..1.04 rows=1 width=194) (actual time=0.011..0.012 rows=1 loops=1)
   Filter: ((city)::text = 'San Francisco'::text)
   Rows Removed by Filter: 2
 Planning time: 0.050 ms
 Execution time: 0.023 ms
(5 rows)
#
#
# EXPLAIN (ANALYZE ON,TIMING ON) SELECT * FROM weather WHERE city='San Francisco';
                                            QUERY PLAN                                             
---------------------------------------------------------------------------------------------------
 Seq Scan on weather  (cost=0.00..1.04 rows=1 width=194) (actual time=0.007..0.008 rows=1 loops=1)
   Filter: ((city)::text = 'San Francisco'::text)
   Rows Removed by Filter: 2
 Planning time: 0.048 ms
 Execution time: 0.021 ms
(5 rows)
#
#
# EXPLAIN (FORMAT JSON) SELECT * FROM weather WHERE city='San Francisco';
                        QUERY PLAN                        
----------------------------------------------------------
 [                                                       +
   {                                                     +
     "Plan": {                                           +
       "Node Type": "Seq Scan",                          +
       "Parallel Aware": false,                          +
       "Relation Name": "weather",                       +
       "Alias": "weather",                               +
       "Startup Cost": 0.00,                             +
       "Total Cost": 1.04,                               +
       "Plan Rows": 1,                                   +
       "Plan Width": 194,                                +
       "Filter": "((city)::text = 'San Francisco'::text)"+
     }                                                   +
   }                                                     +
 ]
(1 row)
#


# 其他示例参考：
https://www.postgresql.org/docs/current/static/using-explain.html#using-explain-basics