Re: Get more from indices.

Hello,

tgl> The problem is that joining isn't the only way that such expansion can
tgl> happen.  Set-returning functions in the targetlist are another way,
tgl> and I'm not sure that there aren't others.  Here's an example that
tgl> I'm pretty sure breaks your patch (though I didn't actually reinstall
tgl> the patch to try it):
tgl> 
tgl> create or replace function rev(n int) returns setof int language plpgsql
tgl> as 'begin for i in reverse n..1 loop return next i; end loop; end';
tgl> 
tgl> create table tt (f1 int primary key, f2 int);
tgl> 
tgl> insert into tt values (1,2), (2,3);
tgl> 
tgl> select f1, rev(f2) from tt order by 1,2;
tgl> 
tgl> Also, even if the row-expansion mechanism is a join, it's certainly
tgl> insufficient to check that the lower-order sort column is an expression
tgl> in variables of the index's table.  Something like "f2 + random()" is
tgl> going to need an explicit sort step anyway.
tgl> 
tgl> These particular objections could be worked around by checking for
tgl> set-returning functions and volatile functions in the lower-order
tgl> ORDER BY expressions.  But I have to say that I think I'm losing
tgl> faith in the entire idea.  I have little confidence that there
tgl> aren't other cases that will break it.

I think that the required condition for all these ordering
problems is generating multiple rows for single input (for a
value of any column of the same table).

If a prefixing set of values correspond to a unique index appears
only once in a result, the result must can be fully-ordered by
the extended pathkeys. This is what this patch stands on. It
never be broken while the precondition is satisfied... I think.

On the other hand, the precondition should be satisfied when all
extended columns are simple Vars of the target table. I believe
Vars cannot produce multiple result. More close checking for
every possibility could be done but it should be a overdone.

The following modification to v7 does this.

=========
diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c
index 380f3ba..233e21c 100644
--- a/src/backend/optimizer/path/pathkeys.c
+++ b/src/backend/optimizer/path/pathkeys.c
@@ -536,7 +536,8 @@ index_pathkeys_are_extensible(PlannerInfo *root,        {            EquivalenceMember *member =
(EquivalenceMember*) lfirst(lc2);
 
-            if (!bms_equal(member->em_relids, index->rel->relids))
+            if (!bms_equal(member->em_relids, index->rel->relids) ||
+                !IsA(member, Var))                continue;            else            {
==========

The result is

postgres=# select f1, rev(f2) from tt order by 1, 2;f1 | rev 
----+----- 1 |   1 1 |   2 2 |   1 2 |   2 2 |   3
==========

regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center