SourceForge & Postgres

I thought the hackers team would be interested in knowing that SourceForge, as
of Friday evening, is running on Postgres. Some 95,000 users and 12,500 Open
Source projects are depending on your stuff, so I hope it's going to be stable
for us. ;-)

Throughout the codebase we're making good use of transactions, subselects, and
foreign keys in all the places I've been wanting them for the past year, but
I'm running into some places where the query optimizer is not using the right
indexes, and sometimes does sequential scans on tables.

Here's a good example. If I remove the ORDER BY (which I didn't care to have),
postgres resorts to a sequential scan of the table, instead of using one of
3 or 4 appropriate indexes. I have an index on group_id, one on
(group_id,status_id) and one on (group_id,status_id,assigned_to)

SELECT
bug.group_id,bug.priority,bug.bug_id,bug.summary,bug.date,users.user_name AS
submitted_by,user2.user_name AS assigned_to_user
FROM bug,users,users user2
WHERE group_id='1'
AND bug.status_id <> '3'
AND users.user_id=bug.submitted_by
AND user2.user_id=bug.assigned_to
--
ORDER BY bug.group_id,bug.status_id
--
LIMIT 51 OFFSET 0;

Tim

--
Founder - PHPBuilder.com / Geocrawler.com
Lead Developer - SourceForge
VA Linux Systems