Re: [HACKERS] Surjective functional indexes

On 09.11.2018 2:27, Tom Lane wrote:
> I wrote:
>> The bigger picture here, and the reason for my skepticism about having
>> any intelligence in the enabling logic, is that there is no scenario
>> in which this code can be smarter than the user about what to do.
>> We have no insight today, and are unlikely to have any in future, about
>> whether a specific index expression is many-to-one or not.
> Hmm ... actually, I take that back. Since we're only interested in this
> for expression indexes, we can expect that statistics will be available
> for the expression index, at least for tables that have been around
> long enough that UPDATE performance is really an exciting topic.
> So you could imagine pulling up the stadistinct numbers for the index
> column(s) and the underlying column(s), and enabling the optimization
> when their ratio is less than $something. Figuring out how to merge
> numbers for multiple columns might be tricky, but it's not going to be
> completely fact-free. (I still think that the cost-estimate logic is
> quite bogus, however.)
>
> Another issue in all this is the cost of doing this work over again
> after any relcache flush. Maybe we could move the responsibility
> into ANALYZE?
>
> BTW, the existing code appears to be prepared to enable this logic
> if *any* index column is an expression, but surely we should do so
> only if they are *all* expressions?
>
> regards, tom lane

From my point of view "auto" value should be default, otherwise it has
not so much sense.
If somebody decides to switch on this optimization for some particular
index, then it will set it to "on", not "auto".
So I agree with your previous opinion, that if this optimization is
disabled by default, then it is enough to have boolean parameter.

Concerning muticolumn indexes: why we should apply this optimization
only if *all* of index columns are expressions?
Assume very simple example: we have some kind of document storage
represented by the following table:

     create table document(owner integer, name text, last_updated
timestamp, description json);

So there are some static document attributes (name, date,...) and some
dynamic, stored in json field.
Consider that most frequently users will search among their own documents.
So we may create index like this:

     create index by_title on documents(owner,(description->>'title'));

Document description may include many attributes which are updated quite
frequently, like "comments", "keywords",...
But "title" is rarely changed, so this optimization will be very useful
for such index.

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