今天应用反应有张表查询报错，报错信息如下
1 2
back=# select max(create_time) from public.tbl_index_table where create_time>='2010-10 -08 '; ERROR: could not read block 41381 of relation 16779/24769/24938: read only 0 of 8192 bytes
看到这个错误信息，首先想到的是表 tbl_index_table 上有坏块，估计需要表重建下。
查看执行计划
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
back= Table "public.tbl_index_table" Column | Type | Modifiers total | integer | logined | integer | logining | integer | http | integer | rawtcp | integer | create_time | timestamp without time zone | not null default now() logincountdesc | character varying | logincountaddr | character varying | not null Indexes: "tbl_index_table_pkey" PRIMARY KEY, btree (create_time, logincountaddr) "index_tbl_index_table_create_time" btree (create_time) back= ERROR: could not read block 41381 of relation 16779 /24769 /24938 : read only 0 of 8192 bytes back= QUERY PLAN Result (cost=0.04 ..0 .05 rows=1 width=0 ) InitPlan -> Limit (cost=0.00 ..0 .04 rows=1 width=8 ) -> Index Scan Backward using index_tbl_index_table_create_time on tbl_index_table (cost =0 .00 ..66 .28 rows =1507 width =8 ) Index Cond: (create_time >= '2010-10-08 00:00:00' ::timestamp without time zone) Filter: (create_time IS NOT NULL ) (6 rows)
发现上面的查询走的索引 index_tbl_index_table_create_time,猜测索引可能有问题。
根据报错信息,从relation后面的数字分析
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
back=# select oid,relname from pg_class where oid=24938; oid | relname -------+----------------------------------------- 24938 | index_tbl_index_table_create_time (1 row) Time: 0.596 ms back=# select oid,relname from pg_class where oid=24769; oid | relname -----+--------- (0 rows) Time: 0.369 ms back=# select oid,relname from pg_class where oid=16779; oid | relname -----+--------- (0 rows)
发现 24938正好是表上的索引 index_tbl_index_table_create_time。
查看索引状态
1 2 3 4 5
back= indexrelid | indrelid | indnatts | indisunique | indisprimary | indisclustered | indisvalid | indcheckxmin | indisready | indkey | indclass | indoption | indexprs | indpred ------------+----------+----------+-------------+--------------+----------------+------------+--------------+------------+--------+----------+-----------+----------+--------- 24938 | 24823 | 1 | f | f | f | t | f | t | 6 | 10053 | 0 | | (1 row)
indisvalid=t 表示索引处于可用状态。
尝试重建索引
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
back = current_query <IDLE> <IDLE> select current_query from pg_stat_activity; <IDLE> <IDLE> (22 rows) back =Timing is on . back =REINDEX Time: 107796.232 ms
索引重建后，查询恢复正常
1 2 3 4 5 6 7 8 9 10 11 12
back=# select max(create_time) from public.tbl_index_table where create_time>='2010-10 -08 '; max ----- (1 row) Time: 73.600 msback=# select pg_size_pretty(pg_relation_size('index_tbl_index_table_create_time')); pg_size_pretty ---------------- 327 MB (1 row)
总结
网上查了下，说是 Postgresql 的bug 2197, 但从上面的处理过程来看，应该是索引上有坏块，索引重建后，查询恢复正常。
原创文章，作者：bd101bd101，如若转载，请注明出处：https://blog.ytso.com/tech/database/236333.html
