bad plan with custom data types
От | Greg Mitchell |
---|---|
Тема | bad plan with custom data types |
Дата | |
Msg-id | 200611212017.26878.gmitchell@atdesk.com обсуждение исходный текст |
Ответы |
Re: bad plan with custom data types
(Tom Lane <tgl@sss.pgh.pa.us>)
Re: bad plan with custom data types (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления:
Предыдущее
От: anil maranДата:
Сообщение: Error in postgresql after crash unable to restart this needs to be posted on -hackers or -bugs, so that tom, oleg and teodor can take a look