Re: Is it possible to select index values ?

On Mon, Feb 1, 2016 at 4:29 PM, Jonathan Vanasco <postgres(at)2xlp(dot)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.

David J.