Dynamic Partial Index

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