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