Thread: when to reindex?
In Oracle, there is a method to determine when it is advisable to rebuild indexes. Are there any guidelines for this in PostgreSQL? I found this but it doesn't indicate at which point an index should be rebuilt other than corruption. http://www.postgresql.org/docs/8.3/interactive/routine-reindex.html Jon
On Fri, Jun 6, 2008 at 9:36 AM, Roberts, Jon <Jon.Roberts@asurion.com> wrote: > In Oracle, there is a method to determine when it is advisable to > rebuild indexes. Are there any guidelines for this in PostgreSQL? > > I found this but it doesn't indicate at which point an index should be > rebuilt other than corruption. > > http://www.postgresql.org/docs/8.3/interactive/routine-reindex.html PostgreSQL isn't quite as finicky about indexes as oracle can be. If you've ever rebuilt a table and forgot to rebuild the indexes in oracle you know what I'm talking about. PostgreSQL generally takes care of indexes pretty well. There are two reasons to reindex in pgsql. The first one is a corrupted index. Note that if you're running on quality hardware, and a properly configured db (i.e. fsync isn't off, etc...) then you shouldn't get corrupted indexes. If you get them quite often, then you've got worse problems than just figuring out when to reindex. The second common situation that requires a reindex is when you suffer from index bloat. This can be caused by certain out of the ordinary update patterns and by vacuum full.
> On Fri, Jun 6, 2008 at 9:36 AM, Roberts, Jon <Jon.Roberts@asurion.com> > wrote: > > In Oracle, there is a method to determine when it is advisable to > > rebuild indexes. Are there any guidelines for this in PostgreSQL? > > > > I found this but it doesn't indicate at which point an index should be > > rebuilt other than corruption. > > > > http://www.postgresql.org/docs/8.3/interactive/routine-reindex.html > > PostgreSQL isn't quite as finicky about indexes as oracle can be. If > you've ever rebuilt a table and forgot to rebuild the indexes in > oracle you know what I'm talking about. > > PostgreSQL generally takes care of indexes pretty well. There are two > reasons to reindex in pgsql. The first one is a corrupted index. > Note that if you're running on quality hardware, and a properly > configured db (i.e. fsync isn't off, etc...) then you shouldn't get > corrupted indexes. If you get them quite often, then you've got worse > problems than just figuring out when to reindex. The second common > situation that requires a reindex is when you suffer from index bloat. > This can be caused by certain out of the ordinary update patterns and > by vacuum full. I am concerned about index bloat. I have an index on a table that is updated with new data frequently and according to this: http://www.postgresql.org/docs/8.3/interactive/routine-reindex.html "Any situation in which the range of index keys changed over time" I will eventually get index bloat. Based on this, I have the fillfactor set lower than the default 90 but this will fill up and it will run slower over time. I want to automate the reindex process but only reindex when needed. I have a pretty large database so I can't reindex everything regardless if it needs it or not. Jon
At 2:02p -0400 on Fri, 06 Jun 2008, Jon Roberts wrote: > Based on this, I have the fillfactor set lower than the default 90 but > this will fill up and it will run slower over time. I want to automate > the reindex process but only reindex when needed. I have a pretty large > database so I can't reindex everything regardless if it needs it or not. Not knowing the core Postgres internals, this is perhaps a naive question: would it be possible to engineer REINDEX so that it could do the job in increments? Rather than recreating the entire index at once, say only create the index for 2% of the table? I suppose analogous to a partial index somehow? Or, assuming the REINDEX is for speed/bloat, not for corruption, perhaps an option to use the old index as a basis, rather than scanning the entire table multiple times as with a CREATE INDEX CONCURRENTLY. I do see REINDEX CONCURRENTLY on the TODO list though. Not exactly the same idea, but ... Kevin
Roberts, Jon wrote: > Based on this, I have the fillfactor set lower than the default 90 but > this will fill up and it will run slower over time. I want to automate > the reindex process but only reindex when needed. I have a pretty large > database so I can't reindex everything regardless if it needs it or not. I'll try to offer a few pointers, but be aware that there might be a MUCH better way of doing things that I'm just not aware of. I'm lucky enough not to need to worry too much about either index bloat or occasional index rebuilds. Anyway, the following query: select relname, relpages from pg_class where relkind = 'i'; will get you a list of your indexes and the associated page counts. Unfortunately it gives you no indication of the actual use of those pages. You might be able to put something together using the `pageinspect' module. For example, this query: select (stats).* FROM (select pageno, bt_page_stats('INDEXNAME', pageno) AS stats from generate_series( 1, (select relpages from pg_class where relname = 'INDEXNAME') - 1 ) as pageno ) AS x; will return details about each page. Using some simple aggregates should then let you get some idea of the space use in the index. For example, replacing the: SELECT (stats).* FROM line in the above query with: SELECT SUM((x).free_size) AS totalfree, SUM((x).page_size) AS totalsize, SUM((x).free_size)::numeric / SUM((x).page_size)::numeric * 100 AS freepercent FROM -- ... rest of prior query ... should return the percentage of free space in the index. This *might* be a good metric for whether a reindex is appropriate. You can probably obtain the configured fill factor on the index from the catalogs somewhere (I don't know how off the top of my head) and compare that to the free space to see if it's excessive. I'm not at all sure that this is correct, so please point out if I've made some incorrect assumption or just misunderstood something. By the way, I also just run into this message: http://unixadmintalk.com/f48/measuring-table-index-bloat-352483/ when doing a quick search. It might be useful. -- Craig Ringer
"Kevin Hunter" <hunteke@earlham.edu> writes: > Or, assuming the REINDEX is for speed/bloat, not for corruption, perhaps > an option to use the old index as a basis, rather than scanning the > entire table multiple times as with a CREATE INDEX CONCURRENTLY. That's been mentioned, it ought to be on the TODO. The trick is determining *when* to use the index and when to use the table -- but that's something the planner already does quite well and we could hopefully leverage that. Note that in typical cases it would be slower. REINDEX scans the table precisely once and sorts it. The sorting will probably have to do multiple passes through temporary files which is presumably what you're referring to. But those passes are still at least sequential. A full index scan has to do random access reads and in many cases read the same page many times to get different records. The cases where it would win would be where you have a lot of dead space in the table (but not in the index), partial indexes which don't cover much of the table, or a table which is already very well clustered (possibly, depending on other factors). -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support!
Gregory Stark wrote: > REINDEX scans the table > precisely once and sorts it. For the bloat, as opposed to corruption, case - what information is needed from the table that is not in the old index? Why would a sequential read of the old index alone (then some processing) not suffice? Thanks, Jeremy Harris
On Sat, Jun 7, 2008 at 8:07 PM, Jeremy Harris <jgh@wizmail.org> wrote:
In Postgres, an index does not hold the livliness information of the rows it is pointing it; that is, there may be a thousand row-pointers in the index, but not all the rows pointed to by those pointers are known to be live. This is an implication of MVCC in Postgres.
So every index lookup has to look at the corresponding heap (aka table) row and decide if that row should be visible to the querying session.
Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
Gregory Stark wrote:For the bloat, as opposed to corruption, case -REINDEX scans the table
precisely once and sorts it.
what information is needed from the table that
is not in the old index? Why would a sequential
read of the old index alone (then some processing)
not suffice?
In Postgres, an index does not hold the livliness information of the rows it is pointing it; that is, there may be a thousand row-pointers in the index, but not all the rows pointed to by those pointers are known to be live. This is an implication of MVCC in Postgres.
So every index lookup has to look at the corresponding heap (aka table) row and decide if that row should be visible to the querying session.
Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device