From: Tom Lane <tgl@sss.pgh.pa.us>
To: Randolf Richardson, DevNet SysOp 29 <rr@8x.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: Expressional Indexes
Date: Fri, 21 Nov 2003 01:45:16 -0500
Message-ID: <9824.1069397116@sss.pgh.pa.us> (raw)
In-Reply-To: <Xns9437E70E5112rr8xca@200.46.204.72>
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>
<Xns9437E70E5112rr8xca@200.46.204.72>
"Randolf Richardson, DevNet SysOp 29" <rr@8x.ca> writes:
> 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).
Okay ...
> 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'));
No, because the above represents a moving cutoff; it will (and should)
be rejected as a non-immutable predicate condition. You could do
something like
CREATE INDEX my_Nov_03_index on my_table (create_date)
WHERE (create_date >= date '2003-11-01');
and then a month from now replace this with
CREATE INDEX my_Dec_03_index on my_table (create_date)
WHERE (create_date >= date '2003-12-01');
bearing in mind that this index can be used with queries that contain
WHERE conditions like "create_date >= some-date-constant". The planner
must be able to convince itself that the right-hand side of the WHERE
condition is >= the cutoff in the index's predicate condition. Since
the planner is not very bright, both items had better be simple DATE
constants, or it won't be able to figure it out ...
> 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.
It won't do anything you could not have done in prior releases using a
custom-built function as the named function of a functional index. In
that sense it's just a notational improvement rather than a real advance
in capability.
(Actually I guess there is an advance in capability: 7.4 lets you build
a multi-column index on multiple expressions, whereas in prior releases
a functional index could only have one index column. Not sure how
important this is in practice though.)
regards, tom lane
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: tgl@sss.pgh.pa.us, rr@8x.ca
Subject: Re: Expressional Indexes
In-Reply-To: <9824.1069397116@sss.pgh.pa.us>
* 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