Re: postgres performance: comparing 2 data centers
От | Rod Taylor |
---|---|
Тема | Re: postgres performance: comparing 2 data centers |
Дата | |
Msg-id | 1086388169.67371.94.camel@jester обсуждение исходный текст |
Ответ на | Re: postgres performance: comparing 2 data centers ("Michael Nonemacher" <Michael_Nonemacher@messageone.com>) |
Список | pgsql-performance |
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.
В списке pgsql-performance по дате отправления: