Re: SourceForge & Postgres

Tim Perdue wrote:
>
> 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;

This is one of my long standing problems with Postgres, and I have
probably pissed of most of the Postgres guys with my views, but.....

Postgres is stubborn about index selection. I have a FAQ on my website.

http://www.mohawksoft.com/postgres/pgindex.html

In short, run vacuum analyze. If that doesn't fix it, it is because the
data being indexed has a lot of key fields that are probably duplicated.
Given a large table with a statistically significant number of records
assigned to a relatively few unique keys, Postgres will likely calculate
that doing a table scan is the best path.

I almost always start postmaster with the "-o -fs" switches because of
this problem.