Re: SORT and Merge Join via Index

On 14/08/13 12:02, Robert James wrote:
> I noticed that when I have an index on (a,b) of table t, and I do an
> SELECT * FROM t ORDER BY a ASC, it doesn't use the index. When I
> create a new index of only a, it does use the index. Why is that?
>
> And, more importantly, when I do a query involving a merge join of
> table t, which requires sorting table t, the planner does the sort
> manually using quicksort, not using the index. The time that step
> takes is identical to the ORDER BY without using the index. What do I
> need to do to have Postgres use the index for the merge join?
>
> (Postgres 8.3)
>
> Thanks!
>
>
It might be that the RAM taken up by an index of (a,b) rather than (a)
triggers the plan to reject it and/or the extra I/O to scan the extra
disk blocks required by the index of (a,b)?

I cringe when I used to gaily use indexes without any regard for these
factors! :-(

Cheers,
Gavin