From: | Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru> |
---|---|
To: | Simon Riggs <simon(at)2ndquadrant(dot)com> |
Cc: | Christoph Berg <myon(at)debian(dot)org>, Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com>, Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Surjective functional indexes |
Date: | 2017-09-14 09:42:42 |
Message-ID: | 6776afb5-2ebd-ea5a-a6f8-ef0898c9f66d@postgrespro.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 13.09.2017 14:00, Simon Riggs wrote:
> On 13 September 2017 at 11:30, Konstantin Knizhnik
> <k(dot)knizhnik(at)postgrespro(dot)ru> 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 right term.
>> From my point of view, "projection" is quite clear notion and not only for
>> mathematics. It is also widely used in IT and especially in DBMSes.
> 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
>
> Yes, I'd rather not have an option at all, just some simple code with
> useful effect, like we have in many other places.
>
Attached please find new version of projection functional index
optimization patch.
I have implemented very simple autotune strategy: now I use table
statistic to compare total number of updates with number of hot updates.
If fraction of hot updates is relatively small, then there is no sense
to spend time performing extra evaluation of index expression and
comparing its old and new values.
Right now the formula is the following:
#define MIN_UPDATES_THRESHOLD 10
#define HOT_RATIO_THRESHOLD 2
if (stat->tuples_updated > MIN_UPDATES_THRESHOLD
&& stat->tuples_updated >
stat->tuples_hot_updated*HOT_RATIO_THRESHOLD)
{
/* If percent of hot updates is small, then disable
projection index function
* optimization to eliminate overhead of extra index
expression evaluations.
*/
ii->ii_Projection = false;
}
This threshold values are pulled out of a hat: I am not sure if this
heuristic is right.
I will be please to get feedback if such approach to autotune is promising.
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Attachment | Content-Type | Size |
---|---|---|
projection-autotune.patch | text/x-patch | 18.6 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Ashutosh Sharma | 2017-09-14 10:02:13 | Re: Warnings "unrecognized node type" for some DDLs with log_statement = 'ddl' |
Previous Message | Amit Kapila | 2017-09-14 09:36:39 | Re: Setting pd_lower in GIN metapage |