On 11/24/15 7:10 AM, Ants Aasma wrote:
> The use case I have in mind is a table containing multiple years worth
> of (approximately) time series data, where overwhelming majority of
> queries are explicitly interested in recent data. Having a partial
> index with WHERE tstamp > $some_recent_tstamp cutting out 90+% of
> tuples was extremely helpful for performance for both index size
> reasons and having to process less tuples. This index needs to be
> periodically rebuilt with a newer timestamp constant, and the rebuild
> would be a lot faster if it could use the existing index to perform an
> index only scan of 10% of data instead of scanning and sorting the
> full table.
There are other cases where you'd want to build an index off an existing
index as well. It's not that uncommon to have small, specialized indexes
that are fully or partially a subset of another index.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com