Re: [HACKERS] Surjective functional indexes

On 09/28/2017 10:10 PM, Robert Haas wrote: On Wed, Sep 13, 2017 at 7:00 AM, Simon Riggs wrote: If we do have an option it won't be using fancy mathematical terminology at all, it would be described in terms of its function, e.g. recheck_on_update +1. I have nothing

Re: [HACKERS] Surjective functional indexes

On Thu, May 25, 2017 at 7:30 PM, Konstantin Knizhnik wrote: > Right now Postgres determines whether update operation touch index or not > based only on set of the affected columns. > But in case of functional indexes such policy quite frequently leads to > unnecessary

Re: [HACKERS] Surjective functional indexes

On Wed, Sep 13, 2017 at 7:00 AM, Simon Riggs wrote: > If we do have an option it won't be using fancy mathematical > terminology at all, it would be described in terms of its function, > e.g. recheck_on_update +1. > Yes, I'd rather not have an option at all, just some

Re: [HACKERS] Surjective functional indexes

On 15 September 2017 at 16:34, Konstantin Knizhnik wrote: > Attached please find yet another version of the patch. Thanks. I'm reviewing it. -- Simon Riggshttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training &

Re: [HACKERS] Surjective functional indexes

On 14.09.2017 18:53, Simon Riggs wrote: It's not going to work, as already mentioned above. Those stats are at table level and very little to do with this particular index. But you've not commented on the design I mention that can work: index relcache. Concerning your idea to check cost of

Re: [HACKERS] Surjective functional indexes

On 14.09.2017 18:53, Simon Riggs wrote: This works by looking at overall stats, and only looks at the overall HOT %, so its too heavyweight and coarse. I suggested storing stat info on the relcache and was expecting you would look at how often the expression evaluates to new == old. If we

Re: [HACKERS] Surjective functional indexes

On 14 September 2017 at 16:37, Konstantin Knizhnik wrote: > > > On 14.09.2017 13:19, Simon Riggs wrote: >> This works by looking at overall stats, and only looks at the overall >> HOT %, so its too heavyweight and coarse. >> >> I suggested storing stat info on the

Re: [HACKERS] Surjective functional indexes

On 14.09.2017 13:19, Simon Riggs wrote: On 14 September 2017 at 10:42, Konstantin Knizhnik wrote: On 13.09.2017 14:00, Simon Riggs wrote: On 13 September 2017 at 11:30, Konstantin Knizhnik wrote: The only reason of all this

Re: [HACKERS] Surjective functional indexes

On 14 September 2017 at 10:42, Konstantin Knizhnik wrote: > > > On 13.09.2017 14:00, Simon Riggs wrote: >> >> On 13 September 2017 at 11:30, Konstantin Knizhnik >> wrote: >> >>> The only reason of all this discussion about terms is that I

Re: [HACKERS] Surjective functional indexes

On 13.09.2017 14:00, Simon Riggs wrote: On 13 September 2017 at 11:30, Konstantin Knizhnik wrote: The only reason of all this discussion about terms is that I need to choose name for correspondent index option. Simon think that we do not need this option at all.

Re: [HACKERS] Surjective functional indexes

On 13.09.2017 14:00, Simon Riggs wrote: On 13 September 2017 at 11:30, Konstantin Knizhnik wrote: The only reason of all this discussion about terms is that I need to choose name for correspondent index option. Simon think that we do not need this option at all.

Re: [HACKERS] Surjective functional indexes

On 13 September 2017 at 11:30, Konstantin Knizhnik wrote: > The only reason of all this discussion about terms is that I need to choose > name for correspondent index option. > Simon think that we do not need this option at all. In this case we should > not worry about

Re: [HACKERS] Surjective functional indexes

On 13.09.2017 13:14, Christoph Berg wrote: Re: Konstantin Knizhnik 2017-09-13 <2393c4b3-2ec4-dc68-4ea9-670597b56...@postgrespro.ru> On 13.09.2017 10:51, Christoph Berg wrote: Re: Konstantin Knizhnik 2017-09-01 + Functional index

Re: [HACKERS] Surjective functional indexes

Re: Konstantin Knizhnik 2017-09-13 <2393c4b3-2ec4-dc68-4ea9-670597b56...@postgrespro.ru> > > > On 13.09.2017 10:51, Christoph Berg wrote: > > Re: Konstantin Knizhnik 2017-09-01 > > > > > + Functional index is based on on projection

Re: [HACKERS] Surjective functional indexes

On 13.09.2017 10:51, Christoph Berg wrote: Re: Konstantin Knizhnik 2017-09-01 + Functional index is based on on projection function: function which extract subset of its argument. + In mathematic such functions are called

Re: [HACKERS] Surjective functional indexes

Re: Konstantin Knizhnik 2017-09-01 > + Functional index is based on on projection function: function which > extract subset of its argument. > + In mathematic such functions are called non-injective. For injective > function if

Re: [HACKERS] Surjective functional indexes

On 12.09.2017 19:28, Simon Riggs wrote: On 1 September 2017 at 09:47, Konstantin Knizhnik wrote: On 01.09.2017 09:25, Simon Riggs wrote: On 1 September 2017 at 05:40, Thomas Munro wrote: On Fri, Jun 9, 2017 at 8:08 PM, Konstantin

Re: [HACKERS] Surjective functional indexes

On 1 September 2017 at 09:47, Konstantin Knizhnik wrote: > > On 01.09.2017 09:25, Simon Riggs wrote: >> >> On 1 September 2017 at 05:40, Thomas Munro >> wrote: >>> >>> On Fri, Jun 9, 2017 at 8:08 PM, Konstantin Knizhnik >>>

Re: [HACKERS] Surjective functional indexes

On 01.09.2017 09:25, Simon Riggs wrote: On 1 September 2017 at 05:40, Thomas Munro wrote: On Fri, Jun 9, 2017 at 8:08 PM, Konstantin Knizhnik wrote: Attached please find rebased version of the patch. Now "projection" attribute is

Re: [HACKERS] Surjective functional indexes

On 1 September 2017 at 05:40, Thomas Munro wrote: > On Fri, Jun 9, 2017 at 8:08 PM, Konstantin Knizhnik > wrote: >> Attached please find rebased version of the patch. >> Now "projection" attribute is used instead of surjective/injective.

Re: [HACKERS] Surjective functional indexes

On Fri, Jun 9, 2017 at 8:08 PM, Konstantin Knizhnik 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

Re: [HACKERS] Surjective functional indexes

Attached please find rebased version of the patch. Now "projection" attribute is used instead of surjective/injective. -- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company diff --git a/doc/src/sgml/ref/create_index.sgml

Re: [HACKERS] Surjective functional indexes

Re: Konstantin Knizhnik 2017-05-30 > > > On 29.05.2017 20:21, Christoph Berg wrote: > > > > I think the term you were looking for is "projection". > > > > https://en.wikipedia.org/wiki/Projection_(set_theory) > > I have already renamed

Re: [HACKERS] Surjective functional indexes

On 29.05.2017 20:21, Christoph Berg wrote: I think the term you were looking for is "projection". https://en.wikipedia.org/wiki/Projection_(set_theory) I have already renamed parameter from "surjective" to "injective". But I am ok to do do one more renaming to "projection" if it will be

Re: [HACKERS] Surjective functional indexes

On 29.05.2017 21:25, Sven R. Kunze wrote: [...] non-surjective functions. non-injective of course -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Surjective functional indexes

On 29.05.2017 19:21, Christoph Berg wrote: I think the term you were looking for is "projection". https://en.wikipedia.org/wiki/Projection_(set_theory) Maybe, I am seeing too much of a connection here but recently Raymond Hettinger held a very interesting talk [1] at PyCon 2017. For those

Re: [HACKERS] Surjective functional indexes

Re: Konstantin Knizhnik 2017-05-29 <592bbd90.3060...@postgrespro.ru> > On 05/27/2017 09:50 PM, Peter Eisentraut wrote: > > On 5/25/17 12:30, Konstantin Knizhnik wrote: > > > Functions like (info->>'name') are named "surjective" ni mathematics. > > A surjective function is one where each value in

Re: [HACKERS] Surjective functional indexes

On 05/27/2017 09:50 PM, Peter Eisentraut wrote: On 5/25/17 12:30, Konstantin Knizhnik wrote: Functions like (info->>'name') are named "surjective" ni mathematics. A surjective function is one where each value in the output type can be obtained by some input value. That's not what you are

Re: [HACKERS] Surjective functional indexes

On 5/25/17 12:30, Konstantin Knizhnik wrote: > Functions like (info->>'name') are named "surjective" ni mathematics. A surjective function is one where each value in the output type can be obtained by some input value. That's not what you are after here. The behavior you are describing is a

Re: [HACKERS] Surjective functional indexes

On 25.05.2017 19:37, Tom Lane wrote: Konstantin Knizhnik writes: My proposal is to check value of function for functional indexes instead of just comparing set of effected attributes. Obviously, for some complex functions it may have negative effect on update

Re: [HACKERS] Surjective functional indexes

On 2017-05-25 12:37:40 -0400, Tom Lane wrote: > Konstantin Knizhnik writes: > > My proposal is to check value of function for functional indexes instead > > of just comparing set of effected attributes. > > Obviously, for some complex functions it may have negative

Re: [HACKERS] Surjective functional indexes

Konstantin Knizhnik writes: > My proposal is to check value of function for functional indexes instead > of just comparing set of effected attributes. > Obviously, for some complex functions it may have negative effect on > update speed. > This is why I have added

[HACKERS] Surjective functional indexes

Right now Postgres determines whether update operation touch index or not based only on set of the affected columns. But in case of functional indexes such policy quite frequently leads to unnecessary index updates. For example, functional index are widely use for indexing JSON data: