Re: Seq scan on big table, episode 2

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(at)optionshouse(dot)com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email