Thread: SORT and Merge Join via Index

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!

On 8/13/13, Robert James <srobertjames@gmail.com> 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)

Interestingly enough, in the JOIN query, if I replace "t" with:
(SELECT f1, f2 FROM t ORDER BY f1 ASC) AS t_
Postgres does use the index, getting the query done in half the time!

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