I’m not big expert on optimizing Pg queries using indexes. Below
you’ll find suggestions for some.
But please run EXPLAIN for each query before creating any index and
after. Without explains we can not help you precisely, just guessing.
Also, please use some real constants in EXPLAIN, random strings and
numbers will generate plans far from reality.
Thanks for the guidance Ruslan, I’ll see what I can find…
After talking to a friend, we’ve seem to have corrected the issue.
The basic problem was the lack of indexes. Perhaps it’s the size of
our RT database.
The problem for us was the sheer number records:
rt3=> select count(*) from GroupMembers;
rt3=> select count(*) from groups;
CREATE INDEX groupmembers_gid on groupmembers ( groupid );
the slow query log is very quiet now…