On 6 May 2018 at 03:16, Neto pr <netopr9@xxxxxxxxx> wrote: > Execution Plan 1 (query execution time 4.7 minutes): > - https://explain.depesz.com/s/Ughh > - Postgresql version 10.1 (default) with index on l_shipdate (table lineitem) > > Execution Plan 2 (query execution time 2.95 minutes): > - https://explain.depesz.com/s/7Zb7 > - Postgresql version 9.5 (version with source code changed by me) with > index on l_orderkey (table lineitem). > > Some doubts > - Difference between GroupAggregate and Finalize GroupAggregate A Finalize Aggregate node is required to combine the Partially Aggregated records. A Partially Aggregated result differs from normal aggregation as the final function of the aggregate is not called during partial aggregation. This allows the finalize aggregate to combine the partially aggregated results then call the final function. Imagine an aggregate like AVG() where it goes and internally calculates the sum and the count of non-null records. A partial aggregate node would return {sum, count}, but a normal aggregate would return {sum / count}. Having {sum, count} allows each partial aggregated result to be combined allowing the average to be calculated with the total_sum / total_count. > - because some algorithms show measurements on "Disk" and others on > "Memory" example: > - External sort Disk: 52784kB > - quicksort Memory: 47770kB Please read about work_mem in https://www.postgresql.org/docs/current/static/runtime-config-resource.html The reason 10.1 is slower with the parallel query is down to the bitmap heap scan going lossy and scanning many more heap pages than it expected. You could solve this in various ways: 1. Increase work_mem enough to prevent the scan from going lossy (see lossy=1605531 in your plan) 2. turn off enable_bitmapscans (set enable_Bitmapscan = off); 3. Cluster the table on l_shipdate Unfortunately, parallel query often will choose to use a parallel plan utilising multiple workers on a less efficient plan when it estimates the cost / n_workers is lower than the cheapest serial plan's cost. This appears to also be a contributor to your problem. You may get the 9.5 performance if you disabled parallel query, or did one of the 3 options above. You may also want to consider using a BRIN index on the l_shipdate instead of a BTREE index. The cost estimation for BRIN may realise that the bitmap heap scan is not a good option, although I'm not sure it'll be better than what the current v10 plan is using. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
