To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "Randolf Richardson, DevNet SysOp 29" <rr@8x.ca>, pgsql-sql@postgresql.org
Subject: Re: Expressional Indexes
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>
In-Reply-To: <9824.1069397116@sss.pgh.pa.us>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 21 Nov 2003 14:03:16 -0500
Message-ID: <87isld35or.fsf@stark.dyndns.tv>
Lines: 44
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii


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