On Fri, 2004-06-04 at 18:07, Michael Nonemacher wrote:
> Slight update:
> Thanks for the replies; this is starting to make a little more sense...
> I've managed to track down the root of the problem to a single query on
> a single table.  I have a query that looks like this:
>        select count(*) from members where group_id = ? and member_id >
> 0;
> The members table contains about 500k rows.  It has an index on
> (group_id, member_id) and on (member_id, group_id).
> It seems like the statistics are wildly different depending on whether
> the last operation on the table was a 'vacuum analyze' or an 'analyze'.

Yes, bad stats are causing it to pick a poor plan (might be better in
7.5), but you're giving it too many options (which doesn't help) and
using diskspace up unnecessarily.

Keep (group_id, member_id)
Remove (member_id, group_id)
Add (member_id)

An index on just member_id is actually going to perform better than
member_id, group_id since it has a smaller footprint on the disk.

Anytime where both group_id and member_id are in the query, the
(group_id, member_id) index will likely be used.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match

Reply via email to