Re: bad plan with custom data types

Greg Mitchell <gmitchell@atdesk.com> writes:
>> What are the available indexes exactly?

> The indices are on (date, model, bucket) and I'm telling it to join on 
> (date, model, bucket, symbol), where date is a constant. My expectation 
> is that it would merge on (model, bucket, symbol) in-order, though the 
> plan shows it having a merge condition (bucket, symbol, model).

Well, that expectation is certainly a fantasy, since the indexscan
output wouldn't be sorted on symbol.  It would be possible to use
(date, model, bucket), or just (model, bucket), as the merge key
and then apply symbol equality as a post-merge "Filter" condition.
The problem here is that after constant propagation the date equality
condition has been dropped as redundant, leaving (model, bucket) or
(model) as the only merge keys that would work, and the planner is not
capable of recognizing that the indexscan outputs can be treated as
sorted that way rather than sorted with date as the major sort key.
Since 8.1 there is some code in there that can make that kind of
deduction with respect to simple indexscan plans, but I now realize
it's in the wrong place to help with mergejoins :-(.

We probably ought to rework things so that this consideration is
understood by the general "pathkey" code rather than being a special
hack in indxpath.c.  Maybe I'm missing something, but offhand it seems
like anytime we've included a constant in a pathkey equivalence set,
we could decide that that pathkey is a no-op and consider a pathkey
list including such a pathkey to be equal to an otherwise-identical
pathkey list omitting the no-op pathkey.

Too big a change to consider for 8.2 at this late date, unfortunately.
I'll try to take a look at it for 8.3.

For the archives: this can be reproduced in the regression database
with

set enable_hashjoin TO 0;
set enable_nestloop TO 0;
set enable_bitmapscan TO 0;
explain select * from tenk1 a join tenk1 b using(thousand, tenthous) where a.thousand = 555;

The sort steps in the resulting plan are redundant, but the planner
fails to see it.  8.1 and 8.2 do understand they don't need a sort for

explain select * from tenk1 where thousand = 555 order by tenthous;
        regards, tom lane