Обсуждение: Is it possible to select index values ?

Поиск
Список
Период
Сортировка

Is it possible to select index values ?

От
Jonathan Vanasco
Дата:
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
(todetermine uniqueness of values, decide if i should change the function, etc).  It would be easier if I could somehow
accessthe index contents than re-create the index data into a temporary table.

Re: Is it possible to select index values ?

От
"David G. Johnston"
Дата:
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.

David J.

Re: Is it possible to select index values ?

От
Jonathan Vanasco
Дата:

On Feb 1, 2016, at 6:58 PM, David G. Johnston wrote:

You can query the statistics portion of the database to get some basic statistics of the form mentioned.

Yeah, i didn't think there would be support.  The stats collector doesn't have the info that I want... it's focused on how the data is used.  I'm more interested in what the data is.

Basically I want to compare the distribution of index "keys".  In the case of a substring index, comparing the distribution at 3,4,5,6,7,8 characters.  based on that, i can run some server tests on different lengths, and the stats collector comes into play.  i'm taking a blind stab on some index contents, and want to be a bit more educated.

anyways, I eventually realized that I may be better just pulling the columns and running some offline analytics (it could be done in sql, but the results would be nicer as a graph).

Re: Is it possible to select index values ?

От
Melvin Davidson
Дата:

Have you given any thought to pulling index column stats from the pg_stats view?

--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.