btw, HOT was introduced in 8.3.
On 6 December 2011 14:51, Daniel Migowski <dmigow...@ikoffice.de> wrote: > > Continuing this talk on general, as requested by Craig. > > I have a functional Index on a table that is relative expensive to calculate. > Now I noticed on every update of even index-unrelated fields of the table the > index function is calculated again and again. > > I currenly understand that if the update moves the row to a new location (no > HOT replacement), the key to the index has to be calculated from the old and > the new row to update the index. > > This is expensive in my case, and useless, if the input to the immutable > index function has not changed in my update statement, and as such the > calculation should always be done just once. In case of HOT replacement, it > hasn't to be done at all. > > I assume, that comparing values to each other is in most times cheaper than > calling a function have these values as parameters. If there is a high cost > on the function (>1000?), it would be a good thing to always check if the > inputs to the function have changed, before calling this function once or > twice. Since I have a lot of functional indexes, I would greatly profit from > an improvement in this area. > > Is anyone interested in implementing this? > > Regards, > Daniel Migowski > ________________________________ ________ > Von: Craig Ringer [ring...@ringerc.id.au] > Gesendet: Sonntag, 4. Dezember 2011 15:02 > Bis: Daniel Migowski > Cc: pgsql-b...@postgresql.org > Betreff: Re: [BUGS] BUG #6325: Useless Index updates > > On 12/04/2011 08:54 PM, dmigow...@ikoffice.de wrote: >> The following bug has been logged on the website: >> >> Bug reference: 6325 >> Logged by: Daniel Migowski >> Email address: dmigow...@ikoffice.de >> PostgreSQL version: 8.3.16 >> Operating system: Linux >> Description: >> >> It seems that an update to a row in a table always removes the element from >> an index and adds it again. Wouldn't it be faster to check for equality of >> the index parameters in the OLD and NEW record first? > > - This isn't a bug report, it's a feature/enhancement request. Please > use the mailing lists. > > - You're reporting this issue against an old patch release of an old > major release. Why not check with 9.1? > > - The index isn't always updated. Check out HOT (introduced in 8.4, the > release after your current one) which reduces unnecessary index > updates in cases where the old and new row can fit on the same > heap page. > > - In most other cases the index update can't be avoided, because > the new and old rows are on different database pages. The old index > entry has to remain in place so that still-running transactions that > can see the old row can still find it in the index, so it can't be > overwritten and instead a new entry has to be added. > >> I have this problem with an functional index using a relative expensive >> index function, and noticed that the index function is always called even if >> the parameter to the index function has not changed. Wouldn't it be better >> to validate that the input to the index functions has not changed, instead >> of calling the index function over and over again? Especially since the >> index functions seems to be called with the new and the old value anyway. > > That's a more interesting one. Perhaps you could write it up in more > detail, with a test case, and submit it to the pgsql-general mailing list? > > This isn't just about functions anyway. Pg would have to compare *all* > inputs to the old index expression to see if they were the same. > Otherwise, in an expression like f(g(x,y),z) Pg would not have any > stored value for the result of g(x,y) to compare against. It'd have to > instead compare (x1,y1,z1) to (x2,y2,z2) and decide that if they were > the same the result of the index expression hadn't changed. > > That's probably possible, but I'm not sure it'd be a win over just > evaluating the expression in most cases. How would Pg know when to do > it? Using function COST parameters? > > Essentially, this isn't as simple as it looks at face value. > >> I can understand that this might be a precaution in the case that the index >> function isn't stable (is it even possible to use such a function for an >> index?) > > No, it isn't possible. Index functions must be immutable, not just > stable, so their output must be determined entirely by their parameters. > At least on newer versions STABLE or VOLATILE functions should be > rejected in index expressions. > > -- > Craig Ringer > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
