First guys, thanks for the help getting my mapped data types together.
They work great until I run a query on a table where one is an index
column. The planner produces really bad plans (uses nested loop where
merge join would work). I ran ANALYZE on each of the tables in the query
before the test.
First, the two tables are indexed by the same key (date, model, bucket).
The types of model, bucket, and symbol are my custom types (they store as
an integer and display as a string, mapped from a table).
When I created the operators, I created the operator= to support merge,
join is set to eqjoinsel.
When I first run explain I see:
explain select * from create_retail_bucket inner join execution using
(date_, model_, bucket, symbol) where create_retail_bucket.date_ =
'20061101'; QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------Merge
Join (cost=1514021.91..1524365.32 rows=14 width=205) Merge Cond: (("outer".bucket = "inner".bucket) AND
("outer".symbol=
"inner".symbol) AND ("outer".model_ = "inner".model_)) -> Sort (cost=1297329.34..1299646.21 rows=926747 width=167)
Sort Key: create_retail_bucket.bucket,
create_retail_bucket.symbol, create_retail_bucket.model_ -> Bitmap Heap Scan on create_retail_bucket
(cost=6810.61..1159975.47 rows=926747 width=167) Recheck Cond: (date_ = '2006-11-01'::date)
-> Bitmap Index Scan on
create_retail_bucket_date_model_bucket_idx
(cost=0.00..6810.61 rows=926747 width=0) Index Cond: (date_ = '2006-11-01'::date) -> Sort
(cost=216692.56..217397.92rows=282143 width=54) Sort Key: execution.bucket, execution.symbol, execution.model_
-> Bitmap Heap Scan on execution (cost=2076.50..191150.06
rows=282143 width=54) Recheck Cond: ('2006-11-01'::date = date_) -> Bitmap Index Scan on
execution_date_model_bucket_idx
(cost=0.00..2076.50 rows=282143 width=0) Index Cond: ('2006-11-01'::date = date_)
Then I disable, bitmap, nested and seqscan and get:
Merge Join (cost=3435041.20..3445384.61 rows=14 width=205) Merge Cond: (("outer".bucket = "inner".bucket) AND
("outer".symbol=
"inner".symbol) AND ("outer".model_ = "inner".model_)) -> Sort (cost=2861383.46..2863700.32 rows=926747 width=167)
Sort Key: create_retail_bucket.bucket,
create_retail_bucket.symbol, create_retail_bucket.model_ -> Index Scan using
create_retail_bucket_date_model_bucket_idx
on create_retail_bucket (cost=0.00..2724029.58 rows=926747
width=167) Index Cond: (date_ = '2006-11-01'::date) -> Sort (cost=573657.75..574363.11 rows=282143
width=54) Sort Key: execution.bucket, execution.symbol, execution.model_ -> Index Scan using
execution_date_model_bucket_idxon execution(cost=0.00..548115.25 rows=282143 width=54) Index Cond:
('2006-11-01'::date= date_)
I don't understand why it re-sorts the data even though the indexes are in
the same order?
Please help!! This is driving me up a wall....
Thanks,
Greg
