Re: bad plan with custom data types

> What are the available indexes exactly? It looks to me from the names
> that the indexes probably *don't* match the sort order the merge is
> using. What I'm wondering is whether the planner should be expected to
> find a merge plan that adapts to the available indexes. In the light
> of morning I doubt this has anything to do with custom data types at
> all, but with the fact that the planner doesn't exhaustively search
> through every possible combination of mergejoin conditions.

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).

> If you turn off enable_sort as well, does it find a sort-free merge
> plan?

Yes, but not a very good one....

TDB=> explain select * from create_retail_bucket inner join execution
using (date_, model_, bucket, symbol) where create_retail_bucket.date_ =
'20061101';

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=2427.93..2077606.20 rows=18 width=205)
Join Filter: ("inner".symbol = "outer".symbol)
-> Bitmap Heap Scan on execution (cost=2427.93..219154.20
rows=323408 width=54)
Recheck Cond: ('2006-11-01'::date = date_)
-> Bitmap Index Scan on execution_date_model_bucket_idx
(cost=0.00..2427.93 rows=323408 width=0)
Index Cond: ('2006-11-01'::date = date_)
-> Index Scan using create_retail_bucket_date_model_bucket_idx on
create_retail_bucket (cost=0.00..5.73 rows=1 width=167)
Index Cond: ((create_retail_bucket.date_ = '2006-11-01'::date)
AND (create_retail_bucket.model_ = "outer".model_) AND
(create_retail_bucket.bucket = "outer".bucket))
(8 rows)