On Mon, Aug 2, 2010 at 5:23 PM, Peter Hussey <peter@labkey.com> wrote:
> I already had effective_cache_size set to 500MB.
>
> I experimented with lowering random_page_cost to 3 then 2. It made no
> difference in the choice of plan that I could see. In the explain analyze
> output the estimated costs of nested loop were in fact lowererd, but so were
> the costs of the hash join plan, and the hash join remained the lowest
> predicted costs in all tests i tried.
What do you get if you set random_page_cost to a small value such as 0.01?
> What seems wrong to me is that the hash join strategy shows almost no
> difference in estimated costs as work_mem goes from 1MB to 500MB. The cost
> function decreases by 1%, but the actual time for the query to execute
> decreases by 86% as work_mem goes from 1MB to 500MB.
Wow. It would be interesting to find out how many batches are being
used. Unfortunately, releases prior to 9.0 don't display that
information.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company