From: Shaun Thomas <sthomas@optionshouse.com>
To: Vincenzo Melandri <vmelandri@imolinfo.it>
Cc: pgsql-performance@postgresql.org
Subject: Re: Seq scan on big table, episode 2
Date: Wed, 31 Oct 2012 08:04:55 -0500
Message-ID: <509121F7.30505@optionshouse.com> (raw)
In-Reply-To: <CAHSd9GfH6sJ4U0632QbRj6T6TaO59OXceaLeMADt6oFPhdr-aw@mail.gmail.com>
References: <CAHSd9GfH6sJ4U0632QbRj6T6TaO59OXceaLeMADt6oFPhdr-aw@mail.gmail.com>
On 10/31/2012 05:55 AM, Vincenzo Melandri wrote:
> on People:
> CREATE UNIQUE INDEX people_pkey ON people USING btree (key1, key2)
> CREATE INDEX people_pkey_hash_loc ON people USING hash (key1);
> CREATE INDEX people_pkey_hash_id ON people USING hash (key2);
I can't say why it would ignore the first index in this particular JOIN,
but you might as well discard both of those hash indexes. Also,
people_pkey_hash_loc is basically pointless anyway, as the database can
use the first column in a multi-column index as if it were a single
column index.
I *can* ask you why you're using HASH indexes, though. They're not WAL
logged, so they can't be replicated, and they're also not crash safe.
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
sthomas@optionshouse.com
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
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: sthomas@optionshouse.com, vmelandri@imolinfo.it
Subject: Re: Seq scan on big table, episode 2
In-Reply-To: <509121F7.30505@optionshouse.com>
* 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