pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
From: Rod Taylor <pg@rbt.ca>
To: Michael Nonemacher <Michael_Nonemacher@messageone.com>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: postgres performance: comparing 2 data centers
Date: Fri, 04 Jun 2004 18:29:29 -0400
Message-ID: <1086388169.67371.94.camel@jester> (raw)
In-Reply-To: <E3A41572DB871B42AB6939873D95E8CA03874D@auscorpex-1.austin.messageone.com>
References: <E3A41572DB871B42AB6939873D95E8CA03874D@auscorpex-1.austin.messageone.com>
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.
view thread (9+ messages) latest in thread
Message-ID: <1086388169.67371.94.camel@jester> Permalink: ../1086388169.67371.94.camel@jester/ Also on: postgresql.org/message-id/1086388169.67371.94.camel@jester ·
reply
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Reply to all the recipients using the --to and --cc options: reply via email To: pgsql-performance@postgresql.org Cc: pg@rbt.ca, Michael_Nonemacher@messageone.com Subject: Re: postgres performance: comparing 2 data centers In-Reply-To: <1086388169.67371.94.camel@jester> * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox