On 6 May 2018 at 03:16, Neto pr <netopr9@gmail.com> 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
