Re: Questions on query planner, join types, and work_mem
| От | Peter Hussey |
|---|---|
| Тема | Re: Questions on query planner, join types, and work_mem |
| Дата | |
| Msg-id | AANLkTik3oY1=F_uC843c84LxBLNdHxZaWdfjxrAtyBeu@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Questions on query planner, join types, and work_mem (Tom Lane <tgl@sss.pgh.pa.us>) |
| Ответы |
Re: Questions on query planner, join types, and work_mem
(Robert Haas <robertmhaas@gmail.com>)
Re: Questions on query planner, join types, and work_mem (Tom Lane <tgl@sss.pgh.pa.us>) Re: Questions on query planner, join types, and work_mem (Hannu Krosing <hannu@2ndquadrant.com>) Re: Questions on query planner, join types, and work_mem ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>) |
| Список | pgsql-performance |
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@sss.pgh.pa.us> wrote:
--
Peter Hussey
LabKey Software
206-667-7193 (office)
206-291-5625 (cell)
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@sss.pgh.pa.us> wrote:
Peter Hussey <peter@labkey.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:My take on it is that the estimate of the hash plan's cost isn't bad;
> " -> 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)"
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)
В списке pgsql-performance по дате отправления: