Re: Expressional Indexes

>> For example, if I want to index on a date field but only have the index
>> keep track of the most recent 30 days (and then create a secondary
>> index for all dates) so as to improve performance on more heavily
>> loaded systems.
>>
>> Am I understanding this new terminology correctly? Thanks in advance.
>
> No, you could do the above using "partial indexes" but it wouldn't work
> very well in this case because the "last 30 days" keeps moving and you
> would have to keep redefining the index periodically.

For the application I will need to develop in the future, it would be
okay for this index to hold data more than 30 days old, and then be
redefined on a monthly basis along with regular database vacuuming (and
other maintenance).

Could this be done with a "partial index" as follows?

CREATE INDEX my_index on my_table (create_date)
WHERE (create_date > age(timestamp '30 days'));

If I've made any mistakes here, please don't hesitate to let me know
because the age() function is new to me.

> It also wouldn't really help performance.

Really? A smaller index would result in fewer comparisons behind-the-
scenes though, wouldn't it?

> Expression Indexes are just more powerful "functional indexes". In 7.3
> they could be used for indexing expressions like "lower(foo)". In 7.4
> they're more powerful and you can index expressions other than simple
> function calls.
[sNip]

So an "Expression Index" could, for example, be used to sort alpha-
numeric data in a case-insensitive manner? I just want to make sure I'm
understanding this correctly.

Thanks.

--
Randolf Richardson - rr(at)8x(dot)ca
Inter-Corporate Computer & Network Services, Inc.
Vancouver, British Columbia, Canada
http://www.8x.ca/

This message originated from within a secure, reliable,
high-performance network ... a Novell NetWare network.