PostgreSQL（三）索引&执行计划

索引：

-------------------------------创建索引-------------------------------https://www.postgresql.org/docs/current/static/sql-createindex.htmlCREATE [ 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_defaultWHERE 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_nameALTER INDEX [ IF EXISTS ] name SET TABLESPACE tablespace_nameALTER INDEX name DEPENDS ON EXTENSION extension_nameALTER 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.htmlEXPLAIN [ ( option [, ...] ) ] statementEXPLAIN [ ANALYZE ] [ VERBOSE ] statementoption:    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