Re: [PERFORM] Problem with grouping, uses Sort and GroupAggregate, HashAggregate is better(?)
-Original Message- From: Dave Dutcher [mailto:[EMAIL PROTECTED] Sent: Sunday, January 14, 2007 5:12 PM To: Rolf Østvik (HA/EXA); pgsql-performance@postgresql.org Subject: RE: [PERFORM] Problem with grouping, uses Sort and GroupAggregate, HashAggregate is better(?) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Rolf Østvik (HA/EXA) Have you tried set enable_sort=off with 8.1.2? I'm not sure if that will change anything because it has to do at least one sort. Its just a lots faster to do a hashagg + small sort than one big sort in this case. (I wonder if there should be enable_groupagg?) Did you mean enable_sort = 'off' for 8.2.1? I tried to set enable_sort = 'off' for both the 8.1.4 server and the 8.2.1 server. Both servers used the same plan as Run 4 and Run 3 respectively. There were of course some changes in the planner cost for the sort steps, but the execution times was of course the same. Regards Rolf Østvik ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] Problem with grouping, uses Sort and GroupAggregate, HashAggregate is better(?)
(now with a more sensible subject) I have been trying to change a many parameters on server versions 7.4.15, 8.1.4, 8.2.0 and 8.2.1. I still hope a have managed to keep my head straigth and that i do not present to much faulty information. The cost estimates generated by the different server versions differ. I have a query which (as far as i can tell) have some strange differences between 8.2.0 8.2.1. I can provide information about that if anyone want it. Generally these parameters are used. default_statistics_target = 10 (4 selected columns is set to 1000) (I have tested with 1000 as default value but that did not have an impact) (analyzed whenever value was changed) shared_buffers = 64000 (512MB) work_mem/sort_mem = variable, see different run's effective_cache_size = 128000 (1G) random_page_cost = 2 cpu_index_tuple_cost = 0.001 cpu_operator_cost= 0.025 cpu_tuple_cost = 0.01 I have tested with different values for random_page_cost and cpu_*_cost but it have not made a difference. I have tried with random_page cost between 1 and 8, and cpu_*_cost with standard value and 50x bigger) Query is: explain analyze select ur.id as ur_id, ur.unit_ref, ur.execution_time, u.serial_number, to_char(ur.start_date_time, '-MM-DD'), count(*) as num_test from uut_result as ur inner join units as u on ur.unit_ref=u.ref inner join step_result as sr on ur.id=sr.uut_result where ur.id between 174000 and 174000+999 group by ur.id, ur.unit_ref, ur.execution_time, u.serial_number, ur.start_date_time -- order by --ur.start_date_time ; NB: order by clause is used in some results below. === Run 1: Detect work_mem setting influence (See also Run 2) - server version 8.2.1 - Query executed without order by clause - work_mem = 8600; QUERY PLAN - GroupAggregate (cost=44857.70..47976.79 rows=95972 width=37) (actual time=1802.716..2017.337 rows=1000 loops=1) - Sort (cost=44857.70..45097.63 rows=95972 width=37) (actual time=1802.461..1892.743 rows=138810 loops=1) Sort Key: ur.id, ur.unit_ref, ur.execution_time, u.serial_number, ur.start_date_time - Nested Loop (cost=0.00..36915.87 rows=95972 width=37) (actual time=0.063..268.186 rows=138810 loops=1) - Nested Loop (cost=0.00..5017.65 rows=981 width=37) (actual time=0.047..11.919 rows=1000 loops=1) - Index Scan using uut_result_pkey on uut_result ur (cost=0.00..1538.77 rows=1000 width=24) (actual time=0.029..1.727 rows=1000 loops=1) Index Cond: ((id = 174000) AND (id = 174999)) - Index Scan using units_pkey on units u (cost=0.00..3.47 rows=1 width=17) (actual time=0.006..0.007 rows=1 loops=1000) Index Cond: (ur.unit_ref = u.ref) - Index Scan using uut_result_key on step_result sr (cost=0.00..30.82 rows=136 width=4) (actual time=0.011..0.125 rows=139 loops=1000) Index Cond: (ur.id = sr.uut_result) Total runtime: 2021.833 ms (12 rows) === Run 2: Detect work_mem setting influence (See also Run 1) - server version 8.2.1 - Query executed without order by clause - work_mem = 8700; QUERY PLAN - HashAggregate (cost=38355.45..39795.03 rows=95972 width=37) (actual time=436.406..439.867 rows=1000 loops=1) - Nested Loop (cost=0.00..36915.87 rows=95972 width=37) (actual time=0.066..256.235 rows=138810 loops=1) - Nested Loop (cost=0.00..5017.65 rows=981 width=37) (actual time=0.049..10.858 rows=1000 loops=1) - Index Scan using uut_result_pkey on uut_result ur (cost=0.00..1538.77 rows=1000 width=24) (actual time=0.031..1.546 rows=1000 loops=1) Index Cond: ((id = 174000) AND (id = 174999)) - Index Scan using units_pkey on units u (cost=0.00..3.47 rows=1 width=17) (actual time=0.005..0.006 rows=1 loops=1000) Index Cond: (ur.unit_ref = u.ref) - Index Scan using uut_result_key on step_result sr (cost=0.00..30.82 rows=136 width=4) (actual time=0.011..0.123 rows=139 loops=1000) Index Cond: (ur.id = sr.uut_result) Total runtime: 441.193 ms (10 rows) === Comment on Run 1 versus Run 2 (adjusted work_mem) === The difference in setup is value of work_mem. Bigger work_mem gave different cost estimates and selected HashAggregate instead of GroupAggregate. Result was a reduced runtime. I guess that is as expected. (One remark, the switchover between different plans on version 8.1.5 was for work_mem values of 6800 and 6900) === Run 3 (with order by clause): Test group by and
Re: [PERFORM] Problem with grouping, uses Sort and GroupAggregate, HashAggregate is better(?)
Computer: Dell PowerEdge 2950 openSUSE Linux 10.1 Intel(R) Xeon 3.00GHz 4GB memory xfs filesystem on SAS disks -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Rolf Østvik (HA/EXA) Sent: Sunday, January 14, 2007 1:44 PM To: pgsql-performance@postgresql.org Subject: [PERFORM] Problem with grouping, uses Sort and GroupAggregate, HashAggregate is better(?) (now with a more sensible subject) I have been trying to change a many parameters on server versions 7.4.15, 8.1.4, 8.2.0 and 8.2.1. I still hope a have managed to keep my head straigth and that i do not present to much faulty information. The cost estimates generated by the different server versions differ. I have a query which (as far as i can tell) have some strange differences between 8.2.0 8.2.1. I can provide information about that if anyone want it. Generally these parameters are used. default_statistics_target = 10 (4 selected columns is set to 1000) (I have tested with 1000 as default value but that did not have an impact) (analyzed whenever value was changed) shared_buffers = 64000 (512MB) work_mem/sort_mem = variable, see different run's effective_cache_size = 128000 (1G) random_page_cost = 2 cpu_index_tuple_cost = 0.001 cpu_operator_cost= 0.025 cpu_tuple_cost = 0.01 I have tested with different values for random_page_cost and cpu_*_cost but it have not made a difference. I have tried with random_page cost between 1 and 8, and cpu_*_cost with standard value and 50x bigger) Query is: explain analyze select ur.id as ur_id, ur.unit_ref, ur.execution_time, u.serial_number, to_char(ur.start_date_time, '-MM-DD'), count(*) as num_test from uut_result as ur inner join units as u on ur.unit_ref=u.ref inner join step_result as sr on ur.id=sr.uut_result where ur.id between 174000 and 174000+999 group by ur.id, ur.unit_ref, ur.execution_time, u.serial_number, ur.start_date_time -- order by --ur.start_date_time ; NB: order by clause is used in some results below. === Run 1: Detect work_mem setting influence (See also Run 2) - server version 8.2.1 - Query executed without order by clause - work_mem = 8600; QUERY PLAN - GroupAggregate (cost=44857.70..47976.79 rows=95972 width=37) (actual time=1802.716..2017.337 rows=1000 loops=1) - Sort (cost=44857.70..45097.63 rows=95972 width=37) (actual time=1802.461..1892.743 rows=138810 loops=1) Sort Key: ur.id, ur.unit_ref, ur.execution_time, u.serial_number, ur.start_date_time - Nested Loop (cost=0.00..36915.87 rows=95972 width=37) (actual time=0.063..268.186 rows=138810 loops=1) - Nested Loop (cost=0.00..5017.65 rows=981 width=37) (actual time=0.047..11.919 rows=1000 loops=1) - Index Scan using uut_result_pkey on uut_result ur (cost=0.00..1538.77 rows=1000 width=24) (actual time=0.029..1.727 rows=1000 loops=1) Index Cond: ((id = 174000) AND (id = 174999)) - Index Scan using units_pkey on units u (cost=0.00..3.47 rows=1 width=17) (actual time=0.006..0.007 rows=1 loops=1000) Index Cond: (ur.unit_ref = u.ref) - Index Scan using uut_result_key on step_result sr (cost=0.00..30.82 rows=136 width=4) (actual time=0.011..0.125 rows=139 loops=1000) Index Cond: (ur.id = sr.uut_result) Total runtime: 2021.833 ms (12 rows) === Run 2: Detect work_mem setting influence (See also Run 1) - server version 8.2.1 - Query executed without order by clause - work_mem = 8700; QUERY PLAN - HashAggregate (cost=38355.45..39795.03 rows=95972 width=37) (actual time=436.406..439.867 rows=1000 loops=1) - Nested Loop (cost=0.00..36915.87 rows=95972 width=37) (actual time=0.066..256.235 rows=138810 loops=1) - Nested Loop (cost=0.00..5017.65 rows=981 width=37) (actual time=0.049..10.858 rows=1000 loops=1) - Index Scan using uut_result_pkey on uut_result ur (cost=0.00..1538.77 rows=1000 width=24) (actual time=0.031..1.546 rows=1000 loops=1) Index Cond: ((id = 174000) AND (id = 174999)) - Index Scan using units_pkey on units u (cost=0.00..3.47 rows=1 width=17) (actual time=0.005..0.006 rows=1 loops=1000) Index Cond: (ur.unit_ref = u.ref) - Index Scan using uut_result_key on step_result sr (cost=0.00..30.82 rows=136
Re: [PERFORM] Problem with grouping, uses Sort and GroupAggregate, HashAggregate is better(?)
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Rolf Østvik (HA/EXA) Have you tried set enable_sort=off with 8.1.2? I'm not sure if that will change anything because it has to do at least one sort. Its just a lots faster to do a hashagg + small sort than one big sort in this case. (I wonder if there should be enable_groupagg?) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Problem with grouping, uses Sort and GroupAggregate, HashAggregate is better(?)
On 14-Jan-07, at 10:34 AM, Rolf Østvik (HA/EXA) wrote: Computer: Dell PowerEdge 2950 openSUSE Linux 10.1 Intel(R) Xeon 3.00GHz 4GB memory xfs filesystem on SAS disks -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Rolf Østvik (HA/EXA) Sent: Sunday, January 14, 2007 1:44 PM To: pgsql-performance@postgresql.org Subject: [PERFORM] Problem with grouping, uses Sort and GroupAggregate, HashAggregate is better(?) (now with a more sensible subject) I have been trying to change a many parameters on server versions 7.4.15, 8.1.4, 8.2.0 and 8.2.1. I still hope a have managed to keep my head straigth and that i do not present to much faulty information. The cost estimates generated by the different server versions differ. I have a query which (as far as i can tell) have some strange differences between 8.2.0 8.2.1. I can provide information about that if anyone want it. Generally these parameters are used. default_statistics_target = 10 (4 selected columns is set to 1000) (I have tested with 1000 as default value but that did not have an impact) (analyzed whenever value was changed) shared_buffers = 64000 (512MB) double shared_buffers work_mem/sort_mem = variable, see different run's effective_cache_size = 128000 (1G) triple effective_cache (which does not actually use memory but tells the planner what it should expect to see in the buffers) random_page_cost = 2 cpu_index_tuple_cost = 0.001 cpu_operator_cost= 0.025 cpu_tuple_cost = 0.01 I have tested with different values for random_page_cost and cpu_*_cost but it have not made a difference. I have tried with random_page cost between 1 and 8, and cpu_*_cost with standard value and 50x bigger) This is a dubious setting to play with. random_page_cost is the ratio of random_seeks vs sequential seeks, 4 is generally the right number, unless you are using a *very* fast disk, or ram disk. Query is: explain analyze select ur.id as ur_id, ur.unit_ref, ur.execution_time, u.serial_number, to_char(ur.start_date_time, '-MM-DD'), count(*) as num_test from uut_result as ur inner join units as u on ur.unit_ref=u.ref inner join step_result as sr on ur.id=sr.uut_result where ur.id between 174000 and 174000+999 group by ur.id, ur.unit_ref, ur.execution_time, u.serial_number, ur.start_date_time -- order by --ur.start_date_time ; NB: order by clause is used in some results below. === Run 1: Detect work_mem setting influence (See also Run 2) - server version 8.2.1 - Query executed without order by clause - work_mem = 8600; QUERY PLAN - GroupAggregate (cost=44857.70..47976.79 rows=95972 width=37) (actual time=1802.716..2017.337 rows=1000 loops=1) - Sort (cost=44857.70..45097.63 rows=95972 width=37) (actual time=1802.461..1892.743 rows=138810 loops=1) Sort Key: ur.id, ur.unit_ref, ur.execution_time, u.serial_number, ur.start_date_time - Nested Loop (cost=0.00..36915.87 rows=95972 width=37) (actual time=0.063..268.186 rows=138810 loops=1) - Nested Loop (cost=0.00..5017.65 rows=981 width=37) (actual time=0.047..11.919 rows=1000 loops=1) - Index Scan using uut_result_pkey on uut_result ur (cost=0.00..1538.77 rows=1000 width=24) (actual time=0.029..1.727 rows=1000 loops=1) Index Cond: ((id = 174000) AND (id = 174999)) - Index Scan using units_pkey on units u (cost=0.00..3.47 rows=1 width=17) (actual time=0.006..0.007 rows=1 loops=1000) Index Cond: (ur.unit_ref = u.ref) - Index Scan using uut_result_key on step_result sr (cost=0.00..30.82 rows=136 width=4) (actual time=0.011..0.125 rows=139 loops=1000) Index Cond: (ur.id = sr.uut_result) Total runtime: 2021.833 ms (12 rows) === Run 2: Detect work_mem setting influence (See also Run 1) - server version 8.2.1 - Query executed without order by clause - work_mem = 8700; QUERY PLAN - HashAggregate (cost=38355.45..39795.03 rows=95972 width=37) (actual time=436.406..439.867 rows=1000 loops=1) - Nested Loop (cost=0.00..36915.87 rows=95972 width=37) (actual time=0.066..256.235 rows=138810 loops=1) - Nested Loop (cost=0.00..5017.65 rows=981 width=37) (actual time=0.049..10.858 rows=1000 loops=1) - Index Scan using uut_result_pkey on uut_result ur (cost=0.00..1538.77 rows=1000 width=24) (actual time=0.031..1.546 rows=1000 loops=1) Index Cond: ((id = 174000) AND (id = 174999)) - Index Scan using units_pkey on units u (cost=0.00..3.47 rows=1 width=17) (actual time=0.005..0.006 rows=1 loops=1000) Index
