Re: Surjective functional indexes

On 12.09.2017 19:28, Simon Riggs wrote:
> On 1 September 2017 at 09:47, Konstantin Knizhnik
> <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
>> On 01.09.2017 09:25, Simon Riggs wrote:
>>> On 1 September 2017 at 05:40, Thomas Munro
>>> <thomas(dot)munro(at)enterprisedb(dot)com> wrote:
>>>> On Fri, Jun 9, 2017 at 8:08 PM, Konstantin Knizhnik
>>>> <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
>>>>> Attached please find rebased version of the patch.
>>>>> Now "projection" attribute is used instead of surjective/injective.
>>>> Hi Konstantin,
>>>>
>>>> This still applies but it doesn't compile after commits 2cd70845 and
>>>> c6293249. You need to change this:
>>>>
>>>> Form_pg_attribute att = RelationGetDescr(indexDesc)->attrs[i];
>>>>
>>>> ... to this:
>>>>
>>>> Form_pg_attribute att = TupleDescAttr(RelationGetDescr(indexDesc),
>>>> i);
>>>>
>>>> Thanks!
>>> Does the patch work fully with that change? If so, I will review.
>>>
>> Attached please find rebased version of the patch.
>> Yes, I checked that it works after this fix.
>> Thank you in advance for review.
> Thanks for the patch. Overall looks sound and I consider that we are
> working towards commit for this.
>
> The idea is that we default "projection = on", and can turn it off in
> case the test is expensive. Why bother to have the option? (No docs at
> all then!) Why not just evaluate the test and autotune whether to make
> the test again in the future? That way we can avoid having an option
> completely. I am imagining collecting values on the relcache entry for
> the index.

Autotune is definitely good thing. But I do not think that excludes
having explicit parameter for manual tuning.
For some functional indexes DBA or programmer knows for sure that it
doesn't perform projection.
For example if it translates or changes encoding of original key. It
seems to me that we should make it possible to
declare this index as non-projective and do not rely on autotune.

Also I have some doubts concerning using autotune in this case. First of
all it is very hard to estimate complexity of test.
How can we measure it? Calculate average execution time? It can vary for
different systems and greatly depends on system load...
Somehow calculate cost of indexed expression? It may be also not always
produce expected result.

Moreover, in some cases test may be not expensive, but still useless, if
index expression specifies one-to-one mapping (for example function
reversing key).
Autotone will never be able to reliable determine that indexed
expression is projection or not.

It seems to be more precise to compare statistic for source column and
index expression.
If them are similar, then most likely index expression is not a
projection...
I will think more about it.

> To implement autotuning we would need to instrument the execution. We
> could then display the collected value via EXPLAIN, so we could just
> then use EXPLAIN in your tests rather than implementing a special
> debug mode just for testing. We could also pass that information thru
> to stats as well.
>

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company