From: Randolf Richardson, DevNet SysOp 29 <rr@8x.ca>
To: pgsql-sql@postgresql.org
Subject: Re: Expressional Indexes
Date: Wed, 19 Nov 2003 06:54:43 +0000 (UTC)
Message-ID: <Xns9437E70E5112rr8xca@200.46.204.72> (raw)
References: <7141E2A2-04BE-11D8-81AE-0005029FC1A7@myrealbox.com>
<200310221239.58302.josh@agliodbs.com>
<18349.1066851898@sss.pgh.pa.us>
<200310221322.26013.josh@agliodbs.com>
<Xns9435D8D7BF744rr8xca@200.46.204.72>
<877k1w6fcv.fsf@stark.dyndns.tv>
>> 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@8x.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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rr@8x.ca
Subject: Re: Expressional Indexes
In-Reply-To: <Xns9437E70E5112rr8xca@200.46.204.72>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox