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. These plans were generated by the EXPLAIN ANALYZE command, and the time of plan 1 was 4.7 minutes and plan 2 was 2.95 minutes. 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 - 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
