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