Re: problems maintaining boolean columns in a large table

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.