POSTGRESQL 中如果你的表文件有损坏,则在查询中会直接告诉你某些文件缺失,你无法对这个表进行查询,或操作. 如果是索引可能就没有这么的简单了.
当一个索引文件有问题的情况下,会遇到什么情况
1 查询的时候明明有索引,但查询的时候大部分情况走全表扫描
2 通过pg_stat_user_tables可以看到 idx_scan 明显比 seq_scan要小的多
3 部分数据有时候无法获得
(postgresql 页面的结构)
当读取信息时会遇到错误,并在错误日志中对数据无法读取的信心进行记录.
通过pg_class 和 pg_namespace 进行查询,发现到底 24643 是那个文件
select n.nspname AS schema, c.relname AS realtion
from pg_class c
inner join pg_namespace n on (c.relnamespace = n.oid) where c.relfilenode = '3456';
文件和物理文件就有了一一对应.
而此时会有一个问题,就是INDEX 和 数据是一体的,到底是数据损坏还是索引损坏的问题. 要验证这个问题,有一个比较笨的方法,就是通过pg_dump的方式把表导出,如果这个表被导出的情况下,则证明表的索引已经损坏了.
如果觉得这样的操作对于大表不是太合理,可以通过查询 explain analyze 的方式来进行,查看查询中的数据结果是否一致.
例如
postgres=# explain analyze select * from pgbench_accounts where aid>1 and aid <10000 ; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on pgbench_accounts
(cost=174.61..21830.00 rows=11126 width=97)
(actual time=1.775..4.469 rows=9998 loops=1)
Recheck Cond: ((aid > 1) AND (aid < 10000))
Heap Blocks: exact=164 -> Bitmap Index Scan on idx_pgbench_accounts (cost=0.00..171.83 rows=11126 width=0) (actual time=1.720..1.720 rows=9998 loops=1)
Index Cond: ((aid > 1) AND (aid < 10000)) Planning time: 0.242 ms Execution time: 5.286 ms (7 rows)
postgres=# set enable_indexscan='off';
SET
postgres=# explain analyze select * from pgbench_accounts where aid >10000000;
QUERY PLAN
Seq Scan on pgbench_accounts (cost=0.00..4889345.00 rows=89592207 width=97) (actual time=1859.666..15070.333 rows=90000000 loops=1)
Filter: (aid > 10000000)
Rows Removed by Filter: 10000000 Planning time: 0.161 ms Execution time: 18394.457 ms (12 rows)
明显两次查询的结果是不一致的,使用索引和不使用索引的结果是不同的. 这已经能证明索引出了问题.
当然可以通过 pg_catcheck 来进行系统的数据的完整性的检查.
在索引出现问题后,我们通过reindex的方式,在一个 maintenance windows 的情况下进行相关的数据索引的建立.
或者采用POSTGRESQL 12 版本中的 reindex concurrently 的方式进行在线的索引建立.
如果你的POSTGRESQL 版本低于12 ,则也可以通过建立一个重复的索引,通过 create index concurrently 的方式,在建立索引后,在清理失效的索引.(PG 11)
