Re: index scan with index cond on first column doesn't recognize sort order of second column

Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:

> On 13 Feb 2003, Greg Stark wrote:
>
> > Here's a corner case where the optimizer is doing a redundant sort. I'm not
> > sure if I'm doing something wrong or if it's just something the optimizer
> > doesn't notice.
>
> I'm guessing that it doesn't realize that in this case the sort is
> redundant since I think it's only necessarily redundant for = singlevalue
> with no ors.

I'm not sure. reading the code there does seem to be a special code path for
ors anyways. This codepath claims to be for non-'or' restriction clauses.

I'm not clear on what truncate_useless_pathkeys is doing. At a guess it's
keeping just the prefix, rather than keeping just the suffix. I think at least
for purposes of merge joins, that this is precisely the wrong thing to do.
Of course in all likelihood that just means I've misinterpreted the code.

If it were changed to just keep the suffix would that break other things?

/*
* 2. Match the index against non-'or' restriction clauses.
*/
restrictclauses = group_clauses_by_indexkey(rel, index);

/*
* 3. Compute pathkeys describing index's ordering, if any, then
* see how many of them are actually useful for this query.
*/
index_pathkeys = build_index_pathkeys(root, rel, index,
ForwardScanDirection);
index_is_ordered = (index_pathkeys != NIL);
useful_pathkeys = truncate_useless_pathkeys(root, rel,
index_pathkeys);

Incidentally, I tried being cleverer and it still doesn't notice the index
path ordering. I think because of the truncate_useless_pathkeys above.

db=> explain select * from cache_foo join (select *, 893 as key_id from foo_bar) as x using (key_id,foo_id) ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Merge Join (cost=4107.53..5119.85 rows=2674 width=40)
Merge Cond: ("outer".foo_id = "inner".foo_id)
-> Index Scan using idx_foo_bar_foo on foo_bar (cost=0.00..859.04 rows=45288 width=8)
-> Sort (cost=4107.53..4114.21 rows=2673 width=32)
Sort Key: cache_foo.foo_id
-> Index Scan using idx_cache_foo_foo on cache_foo (cost=0.00..3955.38 rows=2673 width=32)
Index Cond: (key_id = 893)

--
greg