Am 20.08.2008 um 20:06 schrieb Scott Carey:
Ok, so the problem boils down to the sort at the end.
The query up through the merge join on domain is as fast as its
going to get. The sort at the end however, should not happen
ideally. There are not that many rows returned, and it should
hash_aggregate if it thinks there is enough space to do so.
The query planner is going to choose the sort > agg over the hash-
agg if it estimates the total number of resulting rows to be large
enough so that the hash won't fit in work_mem. However, there
seems to be another factor here based on this:
GroupAggregate (cost=11745105.66..12277396.
81 rows=28704 width=12)"
" -> Sort (cost=11745105.66..11878034.93 rows=53171707 width=12)"
" Sort Key: a."user", b.category"
" -> Merge Join (cost=149241.25..1287278.89 rows=53171707
width=12)"
" Merge Cond: (b.domain = a.domain)"
The planner actually thinks there will only be 28704 rows returned
of width 12. But it chooses to sort 53 million rows before
aggregating. Thats either a bug or there's something else wrong
here. That is the wrong way to aggregate those results no matter
how much work_mem you have unless I'm completely missing something...
You can try rearranging the query just to see if you can work around
this. What happens if you compare the explain on:
select
a."user", b.category, sum(1.0/b.cat_count)::float
from result a, domain_categories b
where a."domain" = b."domain"
and b.depth < 4
and a.results > 100
and a."user" < 30000
group by a."user", b.category
"HashAggregate (cost=1685527.69..1686101.77 rows=28704 width=12)"
" -> Merge Join (cost=148702.25..1286739.89 rows=53171707 width=12)"
" Merge Cond: (b.domain = a.domain)"
" -> Index Scan using domain_categories_domain on
domain_categories b (cost=0.00..421716.32 rows=5112568 width=12)"
" Filter: (depth < 4)"
" -> Sort (cost=148415.16..148513.60 rows=39376 width=8)"
" Sort Key: a.domain"
" -> Bitmap Heap Scan on result a
(cost=1249.93..145409.79 rows=39376 width=8)"
" Recheck Cond: ("user" < 30000)"
" Filter: (results > 100)"
" -> Bitmap Index Scan on result_user_idx
(cost=0.00..1240.08 rows=66881 width=0)"
" Index Cond: ("user" < 30000)"
to
select
c."user", c.category, sum(1.0/c.cat_count)::float
from (select a."user", b.category, b.cat_count
from result a, domain_categories b
where a."domain" = b."domain"
and b.depth < 4
and a.results > 100
and a."user" < 30000 ) c
group by c."user", c.category
"HashAggregate (cost=1685527.69..1686101.77 rows=28704 width=12)"
" -> Merge Join (cost=148702.25..1286739.89 rows=53171707 width=12)"
" Merge Cond: (b.domain = a.domain)"
" -> Index Scan using domain_categories_domain on
domain_categories b (cost=0.00..421716.32 rows=5112568 width=12)"
" Filter: (depth < 4)"
" -> Sort (cost=148415.16..148513.60 rows=39376 width=8)"
" Sort Key: a.domain"
" -> Bitmap Heap Scan on result a
(cost=1249.93..145409.79 rows=39376 width=8)"
" Recheck Cond: ("user" < 30000)"
" Filter: (results > 100)"
" -> Bitmap Index Scan on result_user_idx
(cost=0.00..1240.08 rows=66881 width=0)"
" Index Cond: ("user" < 30000)"
It shouldn't make a difference, but I've seen things like this help
before so its worth a try. Make sure work_mem is reasonably sized
for this test.
It's exactly the same. work_mem was set to 3000MB.
Another thing that won't be that fast, but may avoid the sort, is to
select the subselection above into a temporary table, analyze it,
and then do the outer select. Make sure your settings for temporary
space (temp_buffers in 8.3) are large enough for the intermediate
results (700MB should do it). That won't be that fast, but it will
most likely be faster than sorting 50 million + rows. There are
lots of problems with this approach but it may be worth the
experiment.
I'll try this.
Thanks so far!
