Re: discarding duplicate indexes

On Thu, Dec 20, 2012 at 1:26 AM, Gavin Flower
<GavinFlower(at)archidevsys(dot)co(dot)nz> wrote:
> On 20/12/12 14:57, Josh Kupershmidt wrote:
>
> CREATE TABLE test (id int);
> CREATE INDEX test_idx1 ON test (id);
> CREATE INDEX test_idx2 ON test (id);
>
> I initially misread your example code, but after I realised my mistake, I
> thought of an alternative scenario that might be worth considering.
>
> CREATE TABLE test (id int, int sub, text payload);
> CREATE INDEX test_idx1 ON test (id, sub);
> CREATE INDEX test_idx2 ON test (id);
>
>
> Now test_idx2 is logically included in test_idx1, but if the majority of
> transactions only query on id, then test_idx2 would be more better as it
> ties up less RAM

Well, this situation works without any LIKE ... INCLUDING INDEXES
surprises. If you
CREATE TABLE test_copycat (LIKE test INCLUDING INDEXES);

you should see test_copycat created with both indexes, since
indexParams is considered for this deduplicating.

Josh