Re: discarding duplicate indexes

<div class="moz-cite-prefix">On 12/20/2012 12:26 AM, Gavin Flower wrote:<br /></div><blockquote
cite="mid:50D2CBC4.6070006@archidevsys.co.nz"type="cite"><pre wrap="">CREATE TABLE test (id int, int sub, text
payload);
CREATE INDEX test_idx1 ON test (id, sub);
CREATE INDEX test_idx2 ON test (id);


<big><font face="Liberation Serif, serif"><font size="3"><big>Now <font face="Liberation Mono,
monospace">test_idx2</font>is logically included in <font face="Liberation Mono, monospace">test_idx1</font>, but if
themajority of transactions only query on <font face="Liberation Mono, monospace">id</font>, then <font
face="LiberationMono, monospace">test_idx2</font> would be more better as it ties up less
RAM</big></font></font></big></pre></blockquote><br/> if sub is an integer, that index isn't that much larger.  both
indexesneed to index all the rows, and with the header and block overhead, the extra word isn't that big of a deal.  
aslong as there are some transactions using the other index, most of both of them will likely want to be in memory, so
you'llend up using MORE memory.<br /><br /><br />