On Wed, 2006-02-15 at 11:18, martial.bizel@free.fr wrote:
> Here the result with hashAgg to false :
> Nested Loop (cost=2487858.08..2490896.58 rows=1001 width=34) (actual
> time=1028044.781..1030251.260 rows=1000 loops=1)
> -> Subquery Scan "day" (cost=2487858.08..2487870.58 rows=1000 width=16)
> (actual time=1027996.748..1028000.969 rows=1000 loops=1)
> -> Limit (cost=2487858.08..2487860.58 rows=1000 width=12) (actual
> time=1027996.737..1027999.199 rows=1000 loops=1)
> -> Sort (cost=2487858.08..2487866.47 rows=3357 width=12)
> (actual time=1027996.731..1027998.066 rows=1000 loops=1)
> Sort Key: sum(occurence)
> -> GroupAggregate (cost=2484802.05..2487661.48 rows=3357
> width=12) (actual time=810623.035..914550.262 rows=19422774 loops=1)
> -> Sort (cost=2484802.05..2485752.39 rows=380138
> width=12) (actual time=810612.248..845427.013 rows=36724340 loops=1)
> Sort Key: query
> -> Index Scan using test_date on
> queries_detail_statistics (cost=0.00..2449570.55 rows=380138 width=12) (actual
> time=25.393..182029.205 rows=36724340 loops=1)
> Index Cond: ((date >= '2006-01-01'::date)
> AND (date <= '2006-01-30'::date))
> Filter: (((portal)::text = '1'::text) OR
> ((portal)::text = '2'::text))
> -> Index Scan using query_string_pkey on query_string (cost=0.00..3.01
> rows=1 width=34) (actual time=2.244..2.246 rows=1 loops=1000)
> Index Cond: ("outer".query = query_string.id)
> Total runtime: 1034357.390 ms
OK, in the index scan using test_date, you get 36724340 when the planner
expects 380138. That's off by a factor of about 10, so I'm guessing
that your statistics aren't reflecting what's really in your db. You
said before you'd run analyze, so I'd try increasing the stats target on
that column and rerun analyze to see if things get any better.