Message-ID: <509121F7.30505@optionshouse.com>
Date: Wed, 31 Oct 2012 08:04:55 -0500
From: Shaun Thomas <sthomas@optionshouse.com>
Reply-To: <sthomas@optionshouse.com>
Organization: Peak6
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:16.0) Gecko/20121011 Thunderbird/16.0.1
MIME-Version: 1.0
To: Vincenzo Melandri <vmelandri@imolinfo.it>
CC: <pgsql-performance@postgresql.org>
Subject: Re: Seq scan on big table, episode 2
References: 
 <CAHSd9GfH6sJ4U0632QbRj6T6TaO59OXceaLeMADt6oFPhdr-aw@mail.gmail.com>
In-Reply-To: 
 <CAHSd9GfH6sJ4U0632QbRj6T6TaO59OXceaLeMADt6oFPhdr-aw@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"; format=flowed
content-transfer-encoding: 7bit

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