Tatsuo Ishii <[EMAIL PROTECTED]> writes: > Sure. In my understanding, unlike tables "free/reusable space" is > actually not reused in index. pgstatindex would be usefull to judge if > REINDEX is needed by showing the growth of physical length and > "free/reusable space".
Oh. Hmm, if that's what you want then I do not think an indexscan is the way to go about it. The indexscan will only visit leaf pages (and not, for example, internal nodes of a btree). Also the free-space-counting code you're using seems pretty unworkable since the indexscan is unlikely to visit leaf pages in anything like sequential order. I think the only reasonable way to get useful statistics would be to read the index directly --- page by page, no indexscan, distinguishing leaf pages, internal pages, and overhead pages for yourself. This would require index-AM-specific knowledge about how to tell which type each page is, but I believe all the index AMs make that possible. Also, I'd suggest that visiting the heap is just useless overhead. A person who wants to know whether the heap needs to be vacuumed can get that data from pgstattuple. Reading the heap to check tuple state will make this function orders of magnitude slower, while not producing much useful info that I can see. Something else to think about is how to present the results. As soon as you release this we will have people bleating about how come their btrees always show at least 1/3rd free space :-( unless we can think of a way to highlight the fact that that's the expected loading factor for a btree... regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org