Re: Planner issue on sorting joining of two tables with limit - Mailing list pgsql-performance

I just don't find why it is coincidence. I think that such plan will always produce result ordered by two columns, because such nested index scan always produce this result.
Let's consider some simple example in order to illustrate how this plan works.

t1
id | value
---+------
1  | 0.1
2  | 0.3
3  | 0.2

t2
id | id1 | value
---+-----+------
1  |  2  | 0.2
2  |  1  | 0.9
3  |  2  | 0.6
4  |  1  | 0.7
5  |  1  | 0.4
6  |  3  | 0.2

1) The outer index scan will find the row of t1 with least value using test1_value_idx. It will be row (1, 0.1)
2) The inner index scan will find all the rows in t2 where id1 = 1 using test2_id1_value_idx. But index test2_id1_value_idx have second order by value column and the index scan result will be ordered by value. That's why inner index scan will find rows (5, 1, 0.4), (4, 1, 0.7) and (2, 1, 0.9). And following query output will be produced:
value1  | value2
--------+-------
0.1     | 0.4
0.1     | 0.7
0.1     | 0.9
3) The outer index scan will find the row of t1 with the second value using test1_value_idx. It will be row (3, 0.2)
4) The inner index scan will find all the rows in t2 where id1 = 3 using test2_id1_value_idx. This row is (6, 3, 0.2). The following query output will be produced:
value1  | value2
--------+-------
0.2     | 0.2
5) The outer index scan will find the row of t1 with the third value using test1_value_idx. It will be row (2, 0.3)
6) The inner index scan will find all the rows in t2 where id1 = 2 using test2_id1_value_idx. These rows are (1, 2, 0.2) and (3, 2, 0.6). The following query output will be produced:
value1  | value2
--------+-------
0.3     | 0.2
0.3     | 0.6

And the whole query result is:
value1  | value2
--------+-------
0.1     | 0.4
0.1     | 0.7
0.1     | 0.9
0.2     | 0.2
0.3     | 0.2
0.3     | 0.6

And this result is really ordered by t1.value, t2.value.
I can't find error in my reasoning :)

The query without limit produce similar plan.