????????????????????
??????PostgreSQL?У?????????????????????????????(REINDEX INDEX)?????б?????????B-Tree???????????Щ?????????????????????????????????Щ????????????????????????????????????????д????????????????????????o?????????????????????????????á???????????????￡???????????????????????????????????????????????????????????ü?????????????????Ч???????????????????????????????Σ???
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????汻?????????????????????????????IOЧ??????????????
#1. ???????????в???????6??????????????SQL??佫????????????????????
postgres=# SELECT relname?? pg_relation_size(oid)/1024 || 'K' AS size FROM pg_class WHERE relkind='i' AND relname = 'testtable_idx';
relname     | size
----------------+------
testtable_idx | 1240K
(1 row)
#2. ?????????д???????????
postgres=# DELETE FROM testtable WHERE i > 20000;
DELETE 50006
#3. ????????????????????SQL????????????????????
postgres=# ANALYZE testtable;
ANALYZE
#4. ??ò????????????????????????????δ??????????????????????
postgres=# SELECT pg_relation_size('testtable_idx')/1024 || 'K' AS size;
size
------
1240K
(1 row)
#5. ?????????
postgres=# REINDEX INDEX testtable_idx;
REINDEX
#6. ??????????????????????????п??????????????????????
postgres=# SELECT pg_relation_size('testtable_idx')/1024 || 'K' AS size;
size
------
368K
(1 row)
#7. ?????????????????????????????????????????
postgres=# ANALYZE testtable;
ANALYZE
?????塢??????????????
????1. ?????????????????????????
#relpages????VACUUM??ANALYZE?????DDL??????￡???CREATE INDEX????????????????8K????
postgres=# SELECT relfilenode?? relpages FROM pg_class WHERE relname = 'testtable';
relfilenode | relpages
-------------+----------
16412 |       79
(1 row)
????2. ????????????????????????????????????????
postgres=# SELECT c2.relname?? c2.relpages FROM pg_class c?? pg_class c2?? pg_index i
WHERE c.relname = 'testtable' AND c.oid = i.indrelid AND c2.oid = i.indexrelid
ORDER BY c2.relname;
relname    | relpages
---------------+----------
testtable_idx |       46
(1 row)