pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rod Taylor <ports@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:27:08 -0400
Message-ID: <1086388027.67371.92.camel@jester> (raw)
In-Reply-To: <E3A41572DB871B42AB6939873D95E8CA03874D@auscorpex-1.austin.messageone.com>
References: <E3A41572DB871B42AB6939873D95E8CA03874D@auscorpex-1.austin.messageone.com>

> The members table contains about 500k rows.  It has an index on
> (group_id, member_id) and on (member_id, group_id).


Yes, bad stats are causing it to pick a poor plan, but you're giving it
too many options (which doesn't help) and using space 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.

-- 
Rod Taylor <rbt [at] rbt [dot] ca>

Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/signature.asc




view thread (9+ messages)  latest in thread

Message-ID: <1086388027.67371.92.camel@jester>
Permalink:  ../1086388027.67371.92.camel@jester/
Also on:    postgresql.org/message-id/1086388027.67371.92.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: ports@rbt.ca, Michael_Nonemacher@messageone.com
  Subject: Re: postgres performance: comparing 2 data centers
  In-Reply-To: <1086388027.67371.92.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