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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
