On Mon, Feb 1, 2016 at 4:29 PM, Jonathan Vanasco <postgres@2xlp.com> wrote:
Is it possible to select index values ?
I haven't found any documentation that says "No", but I haven't found anything that says "Yes" either.
The reason - I have a few function indexes that are working as partial indexes. I'd like to run some analytics on them (to determine uniqueness of values, decide if i should change the function, etc). It would be easier if I could somehow access the index contents than re-create the index data into a temporary table.
I'm not optimistic that such a capability exists, though...
If on 9.2 or more recent you might have a chance...I still haven't gotten the hang of the INDEX ONLY planner optimization but in theory when one can be used it can also supply the value for the function's column. The probably is that it is an optimization and thus not guaranteed and you also do not get, that I am aware, a mix - either all column data come from the index or all column data comes from the heap. In the later case there is nothing to pull from the heap and so a new value must be derived.
You can query the statistics portion of the database to get some basic statistics of the form mentioned.