From: Greg Stark <gsstark@mit.edu>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Randolf Richardson, DevNet SysOp 29 <rr@8x.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: Expressional Indexes
Date: 21 Nov 2003 14:03:16 -0500
Message-ID: <87isld35or.fsf@stark.dyndns.tv> (raw)
In-Reply-To: <9824.1069397116@sss.pgh.pa.us>
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>
<9824.1069397116@sss.pgh.pa.us>
Tom Lane <tgl@sss.pgh.pa.us> writes:
> 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 ...
Note that if you're just doing this to speed up regular queries where you have
create_date in some small range, then you'll likely not see much of an
increase. Mainly you'll just save space.
What can be interesting is to create a partial index like this but over a
second unrelated column. Something like:
CREATE INDEX my_dec_03_index on my_table (userid)
WHERE (create_date >= date '2003-11-02');
Then you can do queries like
SELECT * FROM my_table WHERE userid = ? AND create_date >= date '2003-11-02'
And it'll be able to efficiently pull out just those records, even if there
are thousands more records that are older than 2003-11-02.
This avoids having to create a two-column index with a low-selectivity column
like "month".
--
greg
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: gsstark@mit.edu, tgl@sss.pgh.pa.us, rr@8x.ca
Subject: Re: Expressional Indexes
In-Reply-To: <87isld35or.fsf@stark.dyndns.tv>
* 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