记录一则PostgreSQL数据库主键索引不生效的案例
Contents
一 背景说明
一则生产环境的慢SQL，发现日志表上没有任何索引，且数据量也不小，300多万条记录，走的全表扫描的执行计划，通过主键id查询数据耗时4秒多。
数据库是PostgreSQL 9.6，运行在CentOS 7.6的系统上，48颗 CPU、内存64GB。
[root@primarydb ~]# cat /etc/redhat-release
CentOS Linux release 7.6.1810 (Core)
[root@primarydb ~]# free -m
total used free shared buff/cache available
Mem: 63883 5077 3665 19767 55141 38095
Swap: 16379 843 15536
[root@primarydb ~]# su - postgres
Last login: Sat May 7 08:42:26 CST 2022 on pts/2
[postgres@primarydb ~]$ psql
psql (9.6.21)
Type "help" for help.
postgres=# select version();
version
-----------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.21 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
postgres=# 二处理和分析步骤
1 查看现有SQL执行计划
fdp_image_center=> explain analyze SELECT id,receive_source,receive_data,receive_api,receive_dubbo_bean,receive_dubbo_method,deal_status,del_status,failure_code,
failure_reason,remark,create_ts,create_user_id,update_ts,update_user_id FROM t_fdp_mgc_api_receive_log WHERE id='1G2AFM2704EG6815C0C000003E3F1006';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Seq Scan on t_fdp_mgc_api_receive_log (cost=0.00..672426.96 rows=1 width=867) (actual time=1844.985..4114.065 rows=1 loops=1)
Filter: (id = '1G2AFM2704EG6815C0C000003E3F1006'::bpchar)
Rows Removed by Filter: 3889740
Planning time: 0.126 ms
Execution time: 4114.110 ms
(5 rows)
Time: 4115.282 ms
fdp_image_center=>2 查看表结构
fdp_image_center=> \d t_fdp_mgc_api_receive_log
Table "public.t_fdp_mgc_api_receive_log"
Column | Type | Modifiers
----------------------+--------------------------------+---------------------
id | character(32) | not null
receive_source | character varying | not null
receive_data | text |
receive_api | text |
receive_dubbo_bean | character varying |
receive_dubbo_method | character varying |
deal_status | character(1) | default '0'::bpchar
del_status | character(1) | default '0'::bpchar
failure_code | character varying |
failure_reason | character varying |
remark | character varying |
create_ts | timestamp(6) without time zone |
create_user_id | character(32) |
update_ts | timestamp(6) without time zone |
update_user_id | character(32) |
fdp_image_center=> \dt+ t_fdp_mgc_api_receive_log
List of relations
Schema | Name | Type | Owner | Size | Description
--------+---------------------------+-------+------------------+---------+-----------------------------
public | t_fdp_mgc_api_receive_log | table | fdp_image_center | 4899 MB | 影像中心_接口数据接收日志表
(1 row)
fdp_image_center=> select count(*) from t_fdp_mgc_api_receive_log;
count
---------
3889746
(1 row)
Time: 2578.878 ms
fdp_image_center=>目标表上没有任何索引，数据量3889746，表大小4899 MB。
3 添加主键index
fdp_image_center=> alter table t_fdp_mgc_api_receive_log add primary key(id);
ALTER TABLE
fdp_image_center=> \d t_fdp_mgc_api_receive_log
Table "public.t_fdp_mgc_api_receive_log"
Column | Type | Modifiers
----------------------+--------------------------------+---------------------
id | character(32) | not null
receive_source | character varying | not null
receive_data | text |
receive_api | text |
receive_dubbo_bean | character varying |
receive_dubbo_method | character varying |
deal_status | character(1) | default '0'::bpchar
del_status | character(1) | default '0'::bpchar
failure_code | character varying |
failure_reason | character varying |
remark | character varying |
create_ts | timestamp(6) without time zone |
create_user_id | character(32) |
update_ts | timestamp(6) without time zone |
update_user_id | character(32) |
Indexes:
"t_fdp_mgc_api_receive_log_pkey" PRIMARY KEY, btree (id)
fdp_image_center=> explain analyze SELECT id,receive_source,receive_data,receive_api,receive_dubbo_bean,receive_dubbo_method,deal_status,del_status,failure_code,
failure_reason,remark,create_ts,create_user_id,update_ts,update_user_id FROM t_fdp_mgc_api_receive_log WHERE id='1G2AFM2704EG6815C0C000003E3F1006';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Seq Scan on t_fdp_mgc_api_receive_log (cost=0.00..672426.96 rows=1 width=867) (actual time=1844.985..4114.065 rows=1 loops=1)
Filter: (id = '1G2AFM2704EG6815C0C000003E3F1006'::bpchar)
Rows Removed by Filter: 3889740
Planning time: 0.126 ms
Execution time: 4114.110 ms
(5 rows)
Time: 4115.282 ms
fdp_image_center=>添加主键index之后，发现SQL依然走的是全表扫描，主键index不生效。
4 收集表统计信息
fdp_image_center=> analyze verbose t_fdp_mgc_api_receive_log;
INFO: analyzing "public.t_fdp_mgc_api_receive_log"
INFO: "t_fdp_mgc_api_receive_log": scanned 30000 of 623803 pages, containing 187804 live rows and 2128 dead rows; 30000 rows in sample, 3905090 estimated total rows
ANALYZE
Time: 2950.674 ms
fdp_image_center=> explain analyze SELECT id FROM t_fdp_mgc_api_receive_log WHERE id='1G2AFM2704EG6815C0C000003E3F1006';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Seq Scan on t_fdp_mgc_api_receive_log (cost=0.00..672616.62 rows=1 width=33) (actual time=3195.233..4508.299 rows=1 loops=1)
Filter: (id = '1G2AFM2704EG6815C0C000003E3F1006'::bpchar)
Rows Removed by Filter: 3889762
Planning time: 0.357 ms
Execution time: 4508.325 ms
(5 rows)
Time: 4509.674 ms
fdp_image_center=> 收集完统计信息之后，执行计划依然是全表扫描。
到这里，内心感到纳闷儿，咋回事儿，主键索引不生效，更新表统计信息之后，依然不奏效。
5 重建主键约束
fdp_image_center=> alter table t_fdp_mgc_api_receive_log drop constraint t_fdp_mgc_api_receive_log_pkey ;
ALTER TABLE
Time: 1381.749 ms
fdp_image_center=> \d t_fdp_mgc_api_receive_log
Table "public.t_fdp_mgc_api_receive_log"
Column | Type | Modifiers
----------------------+--------------------------------+---------------------
id | character(32) | not null
receive_source | character varying | not null
receive_data | text |
receive_api | text |
receive_dubbo_bean | character varying |
receive_dubbo_method | character varying |
deal_status | character(1) | default '0'::bpchar
del_status | character(1) | default '0'::bpchar
failure_code | character varying |
failure_reason | character varying |
remark | character varying |
create_ts | timestamp(6) without time zone |
create_user_id | character(32) |
update_ts | timestamp(6) without time zone |
update_user_id | character(32) |
fdp_image_center=> alter table t_fdp_mgc_api_receive_log add constraint unique_id primary key(id);
ALTER TABLE
Time: 36688.809 ms
fdp_image_center=> \d t_fdp_mgc_api_receive_log
Table "public.t_fdp_mgc_api_receive_log"
Column | Type | Modifiers
----------------------+--------------------------------+---------------------
id | character(32) | not null
receive_source | character varying | not null
receive_data | text |
receive_api | text |
receive_dubbo_bean | character varying |
receive_dubbo_method | character varying |
deal_status | character(1) | default '0'::bpchar
del_status | character(1) | default '0'::bpchar
failure_code | character varying |
failure_reason | character varying |
remark | character varying |
create_ts | timestamp(6) without time zone |
create_user_id | character(32) |
update_ts | timestamp(6) without time zone |
update_user_id | character(32) |
Indexes:
"unique_id" PRIMARY KEY, btree (id)
fdp_image_center=> vacuum freeze verbose ANALYZE t_fdp_mgc_api_receive_log;
INFO: vacuuming "public.t_fdp_mgc_api_receive_log"
INFO: scanned index "unique_id" to remove 43239 row versions
DETAIL: CPU 0.09s/0.26u sec elapsed 0.35 sec
INFO: "t_fdp_mgc_api_receive_log": removed 43239 row versions in 43234 pages
DETAIL: CPU 1.11s/0.54u sec elapsed 8.53 sec
INFO: index "unique_id" now contains 3890163 row versions in 28045 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "t_fdp_mgc_api_receive_log": found 301 removable, 3138172 nonremovable row versions in 527811 out of 623803 pages
DETAIL: 383 dead row versions cannot be removed yet.
There were 220507 unused item pointers.
Skipped 1 page due to buffer pins.
0 pages are entirely empty.
CPU 9.39s/5.67u sec elapsed 62.76 sec.
INFO: vacuuming "pg_toast.pg_toast_51870"
INFO: scanned index "pg_toast_51870_index" to remove 23 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.57 sec
INFO: "pg_toast_51870": removed 23 row versions in 10 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec
INFO: index "pg_toast_51870_index" now contains 14774 row versions in 43 pages
DETAIL: 23 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_51870": found 7 removable, 14774 nonremovable row versions in 3008 out of 3008 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.12s/0.05u sec elapsed 7.12 sec.
INFO: analyzing "public.t_fdp_mgc_api_receive_log"
INFO: "t_fdp_mgc_api_receive_log": scanned 30000 of 623803 pages, containing 187359 live rows and 14 dead rows; 30000 rows in sample, 3895837 estimated total rows
VACUUM
Time: 73112.829 ms
fdp_image_center=> explain analyze SELECT id,receive_source,receive_data,receive_api,receive_dubbo_bean,receive_dubbo_method,deal_status,del_status,failure_code,
failure_reason,remark,create_ts,create_user_id,update_ts,update_user_id FROM t_fdp_mgc_api_receive_log WHERE id='1G2AFM2704EG6815C0C000003E3F1006';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Seq Scan on t_fdp_mgc_api_receive_log (cost=0.00..672500.96 rows=1 width=869) (actual time=2505.320..4352.022 rows=1 loops=1)
Filter: (id = '1G2AFM2704EG6815C0C000003E3F1006'::bpchar)
Rows Removed by Filter: 3889888
Planning time: 0.541 ms
Execution time: 4352.068 ms
(5 rows)
Time: 4353.649 ms
fdp_image_center=> 此时的我，内心开始凌乱….一通操作，开始怀疑主键约束是不是真的创建了index？重建主键约束，还是不生效….
6 手工添加btree index
fdp_image_center=> create index CONCURRENTLY idx_fdp_mgc_api_receive_log_id on t_fdp_mgc_api_receive_log(id);
CREATE INDEX
Time: 39576.585 ms
fdp_image_center=> \d t_fdp_mgc_api_receive_log
Table "public.t_fdp_mgc_api_receive_log"
Column | Type | Modifiers
----------------------+--------------------------------+---------------------
id | character(32) | not null
receive_source | character varying | not null
receive_data | text |
receive_api | text |
receive_dubbo_bean | character varying |
receive_dubbo_method | character varying |
deal_status | character(1) | default '0'::bpchar
del_status | character(1) | default '0'::bpchar
failure_code | character varying |
failure_reason | character varying |
remark | character varying |
create_ts | timestamp(6) without time zone |
create_user_id | character(32) |
update_ts | timestamp(6) without time zone |
update_user_id | character(32) |
Indexes:
"unique_id" PRIMARY KEY, btree (id)
"idx_fdp_mgc_api_receive_log_id" btree (id)
fdp_image_center=> explain analyze SELECT id FROM t_fdp_mgc_api_receive_log WHERE id='1G2AFM2704EG6815C0C000003E3F1006';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using idx_fdp_mgc_api_receive_log_id on t_fdp_mgc_api_receive_log (cost=0.56..8.57 rows=1 width=33) (actual time=0.070..0.071 rows=1 loops=1)
Index Cond: (id = '1G2AFM2704EG6815C0C000003E3F1006'::bpchar)
Heap Fetches: 0
Planning time: 0.297 ms
Execution time: 0.099 ms
(5 rows)
Time: 1.064 ms
fdp_image_center=>啥情况？通过添加主键约束，进而隐式创建的主键index不生效，反而我手工创建的一个普通btree index却生效了。
7 复盘index信息
fdp_image_center=> select tablename,indexname,indexdef from pg_indexes where tablename='t_fdp_mgc_api_receive_log';
tablename | indexname | indexdef
---------------------------+--------------------------------+--------------------------------------------------------------------------------------------------
t_fdp_mgc_api_receive_log | unique_id | CREATE UNIQUE INDEX unique_id ON public.t_fdp_mgc_api_receive_log USING btree (id)
t_fdp_mgc_api_receive_log | idx_fdp_mgc_api_receive_log_id | CREATE INDEX idx_fdp_mgc_api_receive_log_id ON public.t_fdp_mgc_api_receive_log USING btree (id)
(2 rows)
Time: 15.095 ms
fdp_image_center=> 8 想重现该现象
把该表导出来，然后导入到另外一个库上，想看看该情况是否会重现？
导出表：
pg_dump -h localhost -p 5432 -d fdp_image_center -U fdp_image_center -t t_fdp_mgc_api_receive_log -f t_fdp_mgc_api_receive_log.dmp
删除该表的dump文件里的已有的2个index：vi t_fdp_mgc_api_receive_log.dmp 删掉index信息，保存退出。
新库导入表：
bill_fmt=> \i t_fdp_mgc_api_receive_log.dmp
CREATE TABLE
psql:t_fdp_mgc_api_receive_log.dmp:25: ERROR: must be member of role "fdp_image_center"
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
COPY 3915872
bill_fmt=> \d t_fdp_mgc_api_receive_log
Table "public.t_fdp_mgc_api_receive_log"
Column | Type | Modifiers
----------------------+--------------------------------+---------------------
id | character(32) | not null
receive_source | character varying | not null
receive_data | text |
receive_api | text |
receive_dubbo_bean | character varying |
receive_dubbo_method | character varying |
deal_status | character(1) | default '0'::bpchar
del_status | character(1) | default '0'::bpchar
failure_code | character varying |
failure_reason | character varying |
remark | character varying |
create_ts | timestamp(6) without time zone |
create_user_id | character(32) |
update_ts | timestamp(6) without time zone |
update_user_id | character(32) |
bill_fmt=> \dt+ t_fdp_mgc_api_receive_log
List of relations
Schema | Name | Type | Owner | Size | Description
--------+---------------------------+-------+----------+---------+-----------------------------
public | t_fdp_mgc_api_receive_log | table | bill_fmt | 3396 MB | 影像中心_接口数据接收日志表
(1 row)
bill_fmt=>查看执行计划：
bill_fmt=> explain analyze SELECT id,receive_source,receive_data,receive_api,receive_dubbo_bean,receive_dubbo_method,deal_status,del_status,failure_code,
failure_reason,remark,create_ts,create_user_id,update_ts,update_user_id FROM t_fdp_mgc_api_receive_log WHERE id='1G2AFM2704EG6815C0C000003E3F1006';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Seq Scan on t_fdp_mgc_api_receive_log (cost=0.00..480472.40 rows=1 width=869) (actual time=881.723..6629.848 rows=1 loops=1)
Filter: (id = '1G2AFM2704EG6815C0C000003E3F1006'::bpchar)
Rows Removed by Filter: 3915871
Planning time: 0.095 ms
Execution time: 6629.884 ms
(5 rows)
bill_fmt=> 添加主键约束：
bill_fmt=> alter table t_fdp_mgc_api_receive_log add primary key(id);
ALTER TABLE
bill_fmt=> \d t_fdp_mgc_api_receive_log
Table "public.t_fdp_mgc_api_receive_log"
Column | Type | Modifiers
----------------------+--------------------------------+---------------------
id | character(32) | not null
receive_source | character varying | not null
receive_data | text |
receive_api | text |
receive_dubbo_bean | character varying |
receive_dubbo_method | character varying |
deal_status | character(1) | default '0'::bpchar
del_status | character(1) | default '0'::bpchar
failure_code | character varying |
failure_reason | character varying |
remark | character varying |
create_ts | timestamp(6) without time zone |
create_user_id | character(32) |
update_ts | timestamp(6) without time zone |
update_user_id | character(32) |
Indexes:
"t_fdp_mgc_api_receive_log_pkey" PRIMARY KEY, btree (id)
bill_fmt=> explain analyze SELECT id,receive_source,receive_data,receive_api,receive_dubbo_bean,receive_dubbo_method,deal_status,del_status,failure_code,
failure_reason,remark,create_ts,create_user_id,update_ts,update_user_id FROM t_fdp_mgc_api_receive_log WHERE id='1G2AFM2704EG6815C0C000003E3F1006';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using t_fdp_mgc_api_receive_log_pkey on t_fdp_mgc_api_receive_log (cost=0.56..8.57 rows=1 width=869) (actual time=0.167..0.168 rows=1 loops=1)
Index Cond: (id = '1G2AFM2704EG6815C0C000003E3F1006'::bpchar)
Planning time: 0.370 ms
Execution time: 0.206 ms
(4 rows)
bill_fmt=> 这时，想重现该现象，发现添加主键约束之后，SQL执行计划立即改善。
手工创建btree index:
bill_fmt=> create index CONCURRENTLY idx_t_fdp_mgc_api_receive_log_id on t_fdp_mgc_api_receive_log(id);
CREATE INDEX
bill_fmt=> \d t_fdp_mgc_api_receive_log
Table "public.t_fdp_mgc_api_receive_log"
Column | Type | Modifiers
----------------------+--------------------------------+---------------------
id | character(32) | not null
receive_source | character varying | not null
receive_data | text |
receive_api | text |
receive_dubbo_bean | character varying |
receive_dubbo_method | character varying |
deal_status | character(1) | default '0'::bpchar
del_status | character(1) | default '0'::bpchar
failure_code | character varying |
failure_reason | character varying |
remark | character varying |
create_ts | timestamp(6) without time zone |
create_user_id | character(32) |
update_ts | timestamp(6) without time zone |
update_user_id | character(32) |
Indexes:
"t_fdp_mgc_api_receive_log_pkey" PRIMARY KEY, btree (id)
"idx_t_fdp_mgc_api_receive_log_id" btree (id)
bill_fmt=> explain analyze SELECT id,receive_source,receive_data,receive_api,receive_dubbo_bean,receive_dubbo_method,deal_status,del_status,failure_code,
failure_reason,remark,create_ts,create_user_id,update_ts,update_user_id FROM t_fdp_mgc_api_receive_log WHERE id='1G2AFM2704EG6815C0C000003E3F1006';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_t_fdp_mgc_api_receive_log_id on t_fdp_mgc_api_receive_log (cost=0.56..8.57 rows=1 width=869) (actual time=0.050..0.051 rows=1 loops=1)
Index Cond: (id = '1G2AFM2704EG6815C0C000003E3F1006'::bpchar)
Planning time: 0.416 ms
Execution time: 0.105 ms
(4 rows)
bill_fmt=>再看此时的执行计划，就更滑稽了，原有的主键index不走，PostgreSQL的优化器反而用了新建的btree index！
继续分析：
bill_fmt=> drop index idx_t_fdp_mgc_api_receive_log_id;
DROP INDEX
bill_fmt=> explain analyze SELECT id,receive_source,receive_data,receive_api,receive_dubbo_bean,receive_dubbo_method,deal_status,del_status,failure_code,
failure_reason,remark,create_ts,create_user_id,update_ts,update_user_id FROM t_fdp_mgc_api_receive_log WHERE id='1G2AFM2704EG6815C0C000003E3F1006';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using t_fdp_mgc_api_receive_log_pkey on t_fdp_mgc_api_receive_log (cost=0.56..8.57 rows=1 width=869) (actual time=0.048..0.050 rows=1 loops=1)
Index Cond: (id = '1G2AFM2704EG6815C0C000003E3F1006'::bpchar)
Planning time: 0.226 ms
Execution time: 0.090 ms
(4 rows)
bill_fmt=> 优化器，你也不傻呀。还是用回了主键index。
扫尾工作：
连回到原来的数据库，删掉btree index之后，优化器又识别了主键index，并且生效了。
postgres=# \c fdp_image_center fdp_image_center
You are now connected to database "fdp_image_center" as user "fdp_image_center".
fdp_image_center=> explain analyze SELECT id,receive_source,receive_data,receive_api,receive_dubbo_bean,receive_dubbo_method,deal_status,del_status,failure_code,
failure_reason,remark,create_ts,create_user_id,update_ts,update_user_id FROM t_fdp_mgc_api_receive_log WHERE id='1G2AFM2704EG6815C0C000003E3F1006';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_fdp_mgc_api_receive_log_id on t_fdp_mgc_api_receive_log (cost=0.56..8.57 rows=1 width=869) (actual time=16.280..16.282 rows=1 loops=1)
Index Cond: (id = '1G2AFM2704EG6815C0C000003E3F1006'::bpchar)
Planning time: 83.287 ms
Execution time: 16.397 ms
(4 rows)
fdp_image_center=> \d t_fdp_mgc_api_receive_log
Table "public.t_fdp_mgc_api_receive_log"
Column | Type | Modifiers
----------------------+--------------------------------+---------------------
id | character(32) | not null
receive_source | character varying | not null
receive_data | text |
receive_api | text |
receive_dubbo_bean | character varying |
receive_dubbo_method | character varying |
deal_status | character(1) | default '0'::bpchar
del_status | character(1) | default '0'::bpchar
failure_code | character varying |
failure_reason | character varying |
remark | character varying |
create_ts | timestamp(6) without time zone |
create_user_id | character(32) |
update_ts | timestamp(6) without time zone |
update_user_id | character(32) |
Indexes:
"unique_id" PRIMARY KEY, btree (id)
"idx_fdp_mgc_api_receive_log_id" btree (id)
fdp_image_center=> drop index idx_fdp_mgc_api_receive_log_id;
DROP INDEX
fdp_image_center=> explain analyze SELECT id,receive_source,receive_data,receive_api,receive_dubbo_bean,receive_dubbo_method,deal_status,del_status,failure_code,
failure_reason,remark,create_ts,create_user_id,update_ts,update_user_id FROM t_fdp_mgc_api_receive_log WHERE id='1G2AFM2704EG6815C0C000003E3F1006';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Index Scan using unique_id on t_fdp_mgc_api_receive_log (cost=0.56..8.57 rows=1 width=869) (actual time=21.814..21.816 rows=1 loops=1)
Index Cond: (id = '1G2AFM2704EG6815C0C000003E3F1006'::bpchar)
Planning time: 0.230 ms
Execution time: 21.852 ms
(4 rows)
fdp_image_center=> explain analyze SELECT id,receive_source,receive_data,receive_api,receive_dubbo_bean,receive_dubbo_method,deal_status,del_status,failure_code,
failure_reason,remark,create_ts,create_user_id,update_ts,update_user_id FROM t_fdp_mgc_api_receive_log WHERE id='1G2AFM2704EG6815C0C000003E3F1006';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Index Scan using unique_id on t_fdp_mgc_api_receive_log (cost=0.56..8.57 rows=1 width=869) (actual time=0.056..0.057 rows=1 loops=1)
Index Cond: (id = '1G2AFM2704EG6815C0C000003E3F1006'::bpchar)
Planning time: 0.135 ms
Execution time: 0.095 ms
(4 rows)
fdp_image_center=> 三小结
1 PostgreSQL数据库给表添加主键约束时，会自动在该表上创建主键index。这一点儿跟Oracle数据库不同，Oracle里约束是约束，index是index；
2 PostgreSQL数据库给表添加了主键约束，进而隐式的创建了主键index，可是，该index不生效，更新了表统计信息之后，主键index依然不生效；不得已手工添加了btree index反而生效，纳闷儿？
3 同一个数据库服务器，换1个数据库，重现该现象时，一旦添加主键约束，优化器立即识别了主键index，并且生效了，更纳闷儿?
4 暂且把这个问题现象记录下来，留作以后慢慢再复盘吧。我自己都解释不了，说不定以后复盘时会有更新的收获和认知。
