Re: Get more from indices.

"Etsuro Fujita" <fujita.etsuro@lab.ntt.co.jp> writes:
> Thank you for taking time to look at this patch.  I think it's not
> sufficient to check those things.  Let me explain the reason why this patch
> has that code.  The patch has that code in order to prevent
> build_join_pathkeys() from building incorrect join pathkeys', where the
> pathkeys for a join relation constructed by mergejoin or nestloop join are
> built normally just by using the outer path's pathkeys.  Without that code,
> the patch would produce an incorrect result for such a case.

Ah, thanks for the example.  ISTM that really the issue is that if an
originally-unique row is "expanded" into multiple rows, those rows are
sort peers so far as the unique-index column(s) are concerned, and so
now the lower-order ORDER BY columns do matter after all.

The problem is that joining isn't the only way that such expansion can
happen.  Set-returning functions in the targetlist are another way,
and I'm not sure that there aren't others.  Here's an example that
I'm pretty sure breaks your patch (though I didn't actually reinstall
the patch to try it):

create or replace function rev(n int) returns setof int language plpgsql
as 'begin for i in reverse n..1 loop return next i; end loop; end';

create table tt (f1 int primary key, f2 int);

insert into tt values (1,2), (2,3);

select f1, rev(f2) from tt order by 1,2;

Also, even if the row-expansion mechanism is a join, it's certainly
insufficient to check that the lower-order sort column is an expression
in variables of the index's table.  Something like "f2 + random()" is
going to need an explicit sort step anyway.

These particular objections could be worked around by checking for
set-returning functions and volatile functions in the lower-order
ORDER BY expressions.  But I have to say that I think I'm losing
faith in the entire idea.  I have little confidence that there
aren't other cases that will break it.
        regards, tom lane