Further information is These plans were generated by the EXPLAIN ANALYZE command, and the time of plan 1 (Postgresql 10.1 ) was 4.7 minutes and plan 2 (postgresql 9.5 changed) was 2.95 minutes.
2018-05-05 6:26 GMT-07:00 Neto pr <netopr...@gmail.com>: > Dear all > > Could you help me understand these two execution plans for the same query > (query 3 benchmark TPCH www.tpc.org/tpch), executed in two different > environments of Postgresql, as described below: > Execution Plan 1: > - https://explain.depesz.com/s/Ughh > - Postgresql version 10.1 (default) with index on l_shipdate (table > lineitem) > > Execution Plan 2: > - 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 > - because some algorithms show measurements on "Disk" and others on > "Memory" example: > - External sort Disk: 52784kB > - quicksort Memory: 47770kB > > Because one execution plan was much smaller than the other, considering > that the query is the same and the data are the same. > -------------------------------------------------- > select > l_orderkey, > sum(l_extendedprice * (1 - l_discount)) as revenue, > o_orderdate, > o_shippriority > from > customer, > orders, > lineitem > where > c_mktsegment = 'HOUSEHOLD' > and c_custkey = o_custkey > and l_orderkey = o_orderkey > and o_orderdate < date '1995-03-21' > and l_shipdate > date '1995-03-21' > group by > l_orderkey, > o_orderdate, > o_shippriority > order by > revenue desc, > o_orderdate > -------------------------------------------------- > > best regards > Neto >
