Thread: tsvector_update_trigger performance?
Is tsvector_update_trigger() smart enough to not bother updating a tsvector if the text in that column has not changed?
If not, can I make my own update trigger with something like
I'm seeing very high cpu load on my database server and my current theory is that some of the triggers may be causing it.
If not, can I make my own update trigger with something like
if new.description != old.descriptionor do I need to do it from scratch?
return tsvector_update_trigger('fti_all', 'pg_catalog.english', 'title', 'keywords', 'description');
else
return new;
I'm seeing very high cpu load on my database server and my current theory is that some of the triggers may be causing it.
On Wed, 24 Jun 2009, Chris St Denis wrote:
> Is tsvector_update_trigger() smart enough to not bother updating a tsvector
> if the text in that column has not changed?
no, you should do check yourself. There are several examples in mailing lists.
>
> If not, can I make my own update trigger with something like
>
> if new.description != old.description
> return tsvector_update_trigger('fti_all', 'pg_catalog.english',
> 'title', 'keywords', 'description');
> else
> return new;
>
> or do I need to do it from scratch?
>
>
> I'm seeing very high cpu load on my database server and my current theory is
> that some of the triggers may be causing it.
>
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
Oleg Bartunov wrote: > On Wed, 24 Jun 2009, Chris St Denis wrote: > >> Is tsvector_update_trigger() smart enough to not bother updating a >> tsvector if the text in that column has not changed? > > no, you should do check yourself. There are several examples in mailing lists. Or you could try using the supress_redundant_updates_trigger() function that has been included in 8.4 (should be easy to backport) -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Hi, Le 24 juin 09 à 18:29, Alvaro Herrera a écrit : > Oleg Bartunov wrote: >> On Wed, 24 Jun 2009, Chris St Denis wrote: >> >>> Is tsvector_update_trigger() smart enough to not bother updating a >>> tsvector if the text in that column has not changed? >> >> no, you should do check yourself. There are several examples in >> mailing lists. > > Or you could try using the supress_redundant_updates_trigger() > function > that has been included in 8.4 (should be easy to backport) http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/backports/min_update/ http://blog.tapoueh.org/projects.html#sec9 But it won't handle the case where some other random column has changed, but the UPDATE is not affecting the text indexed... -- dim
Dimitri Fontaine wrote: > Hi, > > Le 24 juin 09 à 18:29, Alvaro Herrera a écrit : >> Oleg Bartunov wrote: >>> On Wed, 24 Jun 2009, Chris St Denis wrote: >>> >>>> Is tsvector_update_trigger() smart enough to not bother updating a >>>> tsvector if the text in that column has not changed? >>> >>> no, you should do check yourself. There are several examples in >>> mailing lists. >> >> Or you could try using the supress_redundant_updates_trigger() function >> that has been included in 8.4 (should be easy to backport) > > http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/backports/min_update/ > http://blog.tapoueh.org/projects.html#sec9 > > But it won't handle the case where some other random column has > changed, but the UPDATE is not affecting the text indexed... Tho this looks useful for some things, it doesn't solve my specific problem any. But thanks for the suggestion anyway. This sounds like something that should just be on by default, not a trigger. Is there some reason it would waste the io of writing a new row to disk if nothing has changed? or is it just considered too much unnecessary overhead to compare them?
On Wed, 2009-06-24 at 21:03 -0700, Chris St Denis wrote: > This sounds like something that should just be on by default, not a > trigger. Is there some reason it would waste the io of writing a new row > to disk if nothing has changed? or is it just considered too much > unnecessary overhead to compare them? I think the theory is that carefully written applications generally do not generate redundant updates in the first place. An application that avoids redundant updates should not have to pay the cost of redundant update detection and elimination. -- Craig Ringer
Also consider on update triggers that you could want to run anyway -- dim Le 25 juin 2009 à 07:45, Craig Ringer <craig@postnewspapers.com.au> a écrit : > On Wed, 2009-06-24 at 21:03 -0700, Chris St Denis wrote: >> This sounds like something that should just be on by default, not a >> trigger. Is there some reason it would waste the io of writing a >> new row >> to disk if nothing has changed? or is it just considered too much >> unnecessary overhead to compare them? > > I think the theory is that carefully written applications generally do > not generate redundant updates in the first place. An application that > avoids redundant updates should not have to pay the cost of redundant > update detection and elimination. > > -- > Craig Ringer >