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

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NestedLoop  (cost=2427.93..2077606.20 rows=18 width=205)   Join Filter: ("inner".symbol = "outer".symbol)   ->  Bitmap
HeapScan 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_idxon 
 
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)