Thread: Seeking performance advice: Index for "recent entries"

I have a table with a timestamptz column for the "effective date/time"
of the row, and need to have some queries that look only for those
entries for which that is in the future or VERY recently - which will
be a small minority of rows. I'm looking at something like:

CREATE INDEX on tablename (effective) where effective>timestamptz
'now'-interval '21 days'

with a possible REINDEX or ALTER INDEX or even DROP INDEX/CREATE INDEX
periodically to "prune" the index. However, Postgres complains:

ERROR:  functions in index predicate must be marked IMMUTABLE

Without the arithmetic, it works fine:

CREATE INDEX ledgernotyet on larcombe.ledger (lid,effective) where
effective>timestamptz 'now'

Is there a way around this? Also, how would I go about pruning the
index, preferably in such a way that the old index can be used?

Thanks!

Chris Angelico

Chris Angelico <rosuav@gmail.com> writes:
> I have a table with a timestamptz column for the "effective date/time"
> of the row, and need to have some queries that look only for those
> entries for which that is in the future or VERY recently - which will
> be a small minority of rows. I'm looking at something like:

> CREATE INDEX on tablename (effective) where effective>timestamptz
> 'now'-interval '21 days'

I think this falls under the rubric of "premature optimization is the
root of all evil".  Just use a plain index on the timestamptz column
and be happy.  Searches that only look at the extremal values of a
column work perfectly well with a full index, because they only need to
examine a small range of the index.

> Is there a way around this? Also, how would I go about pruning the
> index, preferably in such a way that the old index can be used?

And that is exactly the reason why a partial index of this sort isn't a
win --- you'd be expending many extra cycles to keep it trimmed.

            regards, tom lane

On Wed, May 9, 2012 at 2:52 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Chris Angelico <rosuav@gmail.com> writes:
>> I have a table with a timestamptz column for the "effective date/time"
>> of the row, and need to have some queries that look only for those
>> entries for which that is in the future or VERY recently - which will
>> be a small minority of rows. I'm looking at something like:
>
>> CREATE INDEX on tablename (effective) where effective>timestamptz
>> 'now'-interval '21 days'
>
> I think this falls under the rubric of "premature optimization is the
> root of all evil".  Just use a plain index on the timestamptz column
> and be happy.  Searches that only look at the extremal values of a
> column work perfectly well with a full index, because they only need to
> examine a small range of the index.

The index is actually on two columns, an account ID followed by the
effective date - I need to look up whether any particular account has
recent entries. Does that make any difference?

Thanks for the advice. I don't have enough data yet to be able to
measure these things, and it's good to solve problems before they
become critical!

ChrisA

Chris Angelico <rosuav@gmail.com> writes:
> On Wed, May 9, 2012 at 2:52 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I think this falls under the rubric of "premature optimization is the
>> root of all evil". �Just use a plain index on the timestamptz column
>> and be happy. �Searches that only look at the extremal values of a
>> column work perfectly well with a full index, because they only need to
>> examine a small range of the index.

> The index is actually on two columns, an account ID followed by the
> effective date - I need to look up whether any particular account has
> recent entries. Does that make any difference?

Should still work all right, though you might want to check plans and
timings on some test data to be sure.

            regards, tom lane

On Thu, May 10, 2012 at 12:36 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Chris Angelico <rosuav@gmail.com> writes:
>> On Wed, May 9, 2012 at 2:52 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> I think this falls under the rubric of "premature optimization is the
>>> root of all evil".  Just use a plain index on the timestamptz column
>>> and be happy.  Searches that only look at the extremal values of a
>>> column work perfectly well with a full index, because they only need to
>>> examine a small range of the index.
>
>> The index is actually on two columns, an account ID followed by the
>> effective date - I need to look up whether any particular account has
>> recent entries. Does that make any difference?
>
> Should still work all right, though you might want to check plans and
> timings on some test data to be sure.

Thank you! Much appreciated.

ChrisA