bad plan with custom data types

From: Greg Mitchell <gmitchell(at)atdesk(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: bad plan with custom data types
Date: 2006-11-22 01:17:26
Message-ID: 200611212017.26878.gmitchell@atdesk.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.92 rows=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_idx on 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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2006-11-22 02:14:17 Re: 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
Previous Message anil maran 2006-11-22 01:17:18 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