Re: Get more from indices.

Hi Horiguchi-san,

Sorry for not reviewing this patch in the last CF.

(2014/03/10 16:21), Kyotaro HORIGUCHI wrote:
> Oops! I found a bug in this patch. The previous v8 patch missed
> the case that build_index_pathkeys() could build a partial
> pathkeys from the index tlist.
>
> This causes the situation follows,
>
> =======
> =# \d cu11
> Table "public.cu11"
> Column | Type | Modifiers
> --------+---------+-----------
> a | integer | not null
> b | integer | not null
> c | integer |
> d | text |
> Indexes:
> "cu11_a_b_idx" UNIQUE, btree (a, b)
>
> s=# explain (costs off) select * from cu11 order by a, c ,d;
> QUERY PLAN
> ---------------------------------------
> Index Scan using cu11_a_b_idx on cu11
> (1 row)
> =======
>
> Where the simple ORDER BY a, c, d on the table with index on
> columns (a, b) results simple index scan which cannot perform the
> order.
>
> The attached v9 patche is fixed by adding a check for the case
> into index_pathkeys_are_extensible(), and rebase to current HEAD.

Good catch!

ISTM that the biggest concern about this patch would be whether it's
worth complicating the code, because the range of application of the
patch is not so wide as is. So, all we need to do is show important use
cases that prove the effectiveness of the patch. Sorry, I can't come up
with a good idea, though.

Thanks,

Best regards,
Etsuro Fujita