Thread: Dynamic Partial Index
Hello I'm interesting in a partial index for a rows that aren't older than 6 mounts, something like the sentence below: -------------------------------------------------------------------------------------------------------------------------------------------------- /CREATE INDEX name_for_the_index ON table (the_column_of_type_date) WHERE ( the_column_of_type_date > (current_date - interval '6 month')::date );/ -------------------------------------------------------------------------------------------------------------------------------------------------- But this is not posible, I receive the next error: -------------------------------------------------------------------------------------------------------------------------------------------------- ERROR: functions in index predicate must be marked IMMUTABLE -------------------------------------------------------------------------------------------------------------------------------------------------- So, what is the best solution for my problem? Thank you, Gustavo
On Thu, 2006-08-17 at 20:50 +0300, gustavo halperin wrote: > I'm interesting in a partial index for a rows that aren't older than 6 > mounts, something like the sentence below: > -------------------------------------------------------------------------------------------------------------------------------------------------- > /CREATE INDEX name_for_the_index ON table (the_column_of_type_date) > > WHERE ( the_column_of_type_date > (current_date - interval '6 > month')::date );/ > -------------------------------------------------------------------------------------------------------------------------------------------------- > But this is not posible, I receive the next error: > -------------------------------------------------------------------------------------------------------------------------------------------------- > ERROR: functions in index predicate must be marked IMMUTABLE > -------------------------------------------------------------------------------------------------------------------------------------------------- > So, what is the best solution for my problem? Create an index on the table, and then periodically move records into a separate archive table. Regards, Jeff Davis
Jeff Davis wrote: > On Thu, 2006-08-17 at 20:50 +0300, gustavo halperin wrote: > >> I'm interesting in a partial index for a rows that aren't older than 6 >> mounts, something like the sentence below: >> -------------------------------------------------------------------------------------------------------------------------------------------------- >> /CREATE INDEX name_for_the_index ON table (the_column_of_type_date) >> >> WHERE ( the_column_of_type_date > (current_date - interval '6 >> month')::date );/ >> -------------------------------------------------------------------------------------------------------------------------------------------------- >> But this is not posible, I receive the next error: >> -------------------------------------------------------------------------------------------------------------------------------------------------- >> ERROR: functions in index predicate must be marked IMMUTABLE >> -------------------------------------------------------------------------------------------------------------------------------------------------- >> So, what is the best solution for my problem? >> > > Create an index on the table, and then periodically move records into a > separate archive table. > > Regards, > Jeff Dave Thanks, but I have a question. If the table is a BIIIIIIG table, use your solution is still a good idea ?? What about to create a partial INDEX for the really current date (and not using the function current_date) and periodically dropped and created it with the current day again and again ?? Thank you again, Gustavo
On Fri, 2006-08-18 at 00:19 +0300, gustavo halperin wrote: > > Create an index on the table, and then periodically move records into a > > separate archive table. > > > > Regards, > > Jeff Dave > Thanks, but I have a question. If the table is a BIIIIIIG table, use > your solution is still a good idea ?? What about to create a partial > INDEX for the really current date (and not using the function > current_date) and periodically dropped and created it with the current > day again and again ?? > If you continue to drop and recreate an index like that, it will need to scan the table during the creation of the index. That will take a while on a large table. You might be better off just using a normal index. To search the index only takes log(n) time. What problem are you currently having with a normal index? The downside of a normal index on a large table is that the index will grow large and consume space. I think the solution is to move seldom- accessed records to a separate archive table. That way, you don't ever have to scan the archive table unless you do a search in the archives. Regards, Jeff Davis
Jeff Davis wrote: > On Fri, 2006-08-18 at 00:19 +0300, gustavo halperin wrote: > >>> Create an index on the table, and then periodically move records into a >>> separate archive table. >>> >>> Regards, >>> Jeff Dave >>> >> Thanks, but I have a question. If the table is a BIIIIIIG table, use >> your solution is still a good idea ?? What about to create a partial >> INDEX for the really current date (and not using the function >> current_date) and periodically dropped and created it with the current >> day again and again ?? >> >> > > If you continue to drop and recreate an index like that, it will need to > scan the table during the creation of the index. That will take a while > on a large table. > > You might be better off just using a normal index. To search the index > only takes log(n) time. What problem are you currently having with a > normal index? > > The downside of a normal index on a large table is that the index will > grow large and consume space. I think the solution is to move seldom- > accessed records to a separate archive table. That way, you don't ever > have to scan the archive table unless you do a search in the archives. > > Regards, > Jeff Davi OK, I become totally convinced now. Thank you, Gustavo