From: | Greg Mitchell <gmitchell(at)atdesk(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: bad plan with custom data types |
Date: | 2006-11-22 16:41:10 |
Message-ID: | 45647DA6.2030402@atdesk.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
> 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
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=2427.93..2077606.20 rows=18 width=205)
Join Filter: ("inner".symbol = "outer".symbol)
-> Bitmap Heap Scan 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_idx on
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Joshua D. Drake | 2006-11-22 16:46:31 | Re: Open source databases '60 per cent cheaper' |
Previous Message | Teodor Sigaev | 2006-11-22 16:11:52 | Re: Tsearch + polish ispell + polish locale |