pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rod Taylor <pg@rbt.ca>
To: Tomasz Myrta <jasiek@klaster.net>
Cc: Scott Marlowe <scott.marlowe@ihs.com>
Cc: Rigmor Ukuhe <rigmor.ukuhe@finestmedia.com>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: "Overlaping" indexes
Date: Mon, 02 Feb 2004 14:17:52 -0500
Message-ID: <1075749471.308.105.camel@jester> (raw)
In-Reply-To: <401E9A37.1070909@klaster.net>
References: <Pine.LNX.4.33.0402021129520.19673-100000@css120.ihs.com>
	<401E9A37.1070909@klaster.net>

On Mon, 2004-02-02 at 13:43, Tomasz Myrta wrote:
> Dnia 2004-02-02 19:30, Użytkownik scott.marlowe napisał:
> > Not entirely, since it only has to sort two columns, it will be smaller, 
> > and will therefore be somewhat faster.
> 
> Can you say something more about it? Will it be enough faster to keep 
> them both? Did anyone make such tests?

You can actually come up with test cases where both indexes are useful.
The three column index will have more data to sift through. That said,
having both indexes used means there is less ram available for cache.

The biggest mistake I see is people doing everything they can to
optimize a single query, then they optimize the next query, etc.

When you consider the entire set of queries, those two indexes are very
likely to slow select throughput down due to increased memory
requirements and the system hitting disk a little more often.

It's similar to the mistake of benchmarking a set of 1000 row tables and
optimizing memory settings for that, then using that configuration on
the 10M row tables in production.





view thread (6+ messages)

Message-ID: <1075749471.308.105.camel@jester>
Permalink:  ../1075749471.308.105.camel@jester/
Also on:    postgresql.org/message-id/1075749471.308.105.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, jasiek@klaster.net, scott.marlowe@ihs.com, rigmor.ukuhe@finestmedia.com
  Subject: Re: "Overlaping" indexes
  In-Reply-To: <1075749471.308.105.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