Timo Klecker wrote:
could you post your trigger function? When you need to rebuild the index,
you could disable the trigger setting the flag if the article is modified.
This could speed up your UPDATE.
Embarrassingly, when I checked, I found that I'd never gotten around to
writing that particular trigger function... (It's just being handled at
the app level).
However, there _is_ a trigger function which sets another flag somewhere
which I bet is responsible for a lot of the time... it sets a "modified"
flag on any journalist associated with the article:
-------------------------------
-- article table trigger
CREATE OR REPLACE FUNCTION article_setjournomodified_onupdate() RETURNS
TRIGGER AS $$
BEGIN
-- whenever article is modified, set the modified flag on any
attributed journos
UPDATE journo SET modified=true WHERE id IN (SELECT journo_id FROM
journo_attr WHERE article_id=NEW.id);
return NULL;
END;
$$ LANGUAGE 'plpgsql';
CREATE TRIGGER article_update AFTER UPDATE ON article FOR EACH ROW
EXECUTE PROCEDURE article_setjournomodified_onupdate();
-------------------------------
(excuse the bad linebreaks!)
I bet the subselect in that trigger slows things down.
"article_id" in journo attr is a foreign key:
"journo_attr_article_id_fkey" FOREIGN KEY (article_id) REFERENCES
article(id) ON DELETE CASCADE
Can the SELECT use such a foreign key index to speed things up? Or do I
need to explicitly add another index? (and yes, I know that's a stupid
newbie question!)
Either way, I'll have a go at disabling the trigger to see what impact
it has on the bulk update of 'article.needs_indexing'!
Actually, I think it's a good argument for moving the needs_indexing
flag out of the article table - modifying any other article fields
should cause attributed journos to be marked 'modified', but the
'needs_indexing' doesn't need to do this - it's just a little
implementation detail rather than real data...
(and the same goes for 'journo.modified'!)
Thanks,
Ben.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
