Re: Questions on query planner, join types, and work_mem

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 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.

My questions are still
1) Does the planner have any component of cost calculations based on the
size of work_mem, and if so why do those calculations seem to have so
little effect here?

2) Why is the setting of work_mem something left to the admin and/or
developer? Couldn't the optimizer say how much it thinks it needs to build
a hash table based on size of the keys and estimated number of rows?

It is difficult for a software development platform like ours to take
advantage of suggestions to set work_mem, or to change the cost function, or
turn on/off join strategies for individual queries. The SQL we issue is
formed by user interaction with the product and rarely static. How would we
know when to turn something on or off? That's why I'm looking for a
configuratoin solution that I can set on a database-wide basis and have it
work well for all queries.

thanks
Peter

On Fri, Jul 30, 2010 at 7:03 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Peter Hussey <peter(at)labkey(dot)com> writes:
> > Using the default of 1MB work_mem, the planner chooses a hash join plan :
> > "Hash Left Join (cost=252641.82..11847353.87 rows=971572 width=111)
> (actual
> > time=124196.670..280461.604 rows=968080 loops=1)"
> > ...
> > For the same default 1MB work_mem, a nested loop plan is better
> > "Nested Loop Left Join (cost=8.27..15275401.19 rows=971572 width=111)
> > (actual time=145.015..189957.023 rows=968080 loops=1)"
> > ...
>
> Hm. A nestloop with nearly a million rows on the outside is pretty
> scary. The fact that you aren't unhappy with that version of the plan,
> rather than the hash, indicates that the "object" table must be
> fully cached in memory, otherwise the repeated indexscans would be a
> lot slower than this:
>
> > " -> Index Scan using uq_object on object obj (cost=0.00..3.51 rows=1
> > width=95) (actual time=0.168..0.170 rows=1 loops=968080)"
> > " Index Cond: ((sd.lsid)::text = (obj.objecturi)::text)"
>
> My take on it is that the estimate of the hash plan's cost isn't bad;
> what's bad is that the planner is mistakenly estimating the nestloop as
> being worse. What you need to do is adjust the planner's cost
> parameters so that it has a better idea of the true cost of repeated
> index probes in your environment. Crank up effective_cache_size if
> you didn't already, and experiment with lowering random_page_cost.
> See the list archives for more discussion of these parameters.
>
> regards, tom lane
>

--
Peter Hussey
LabKey Software
206-667-7193 (office)
206-291-5625 (cell)