Re: comparison between 2 execution plans

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 <netoprbr9(at)gmail(dot)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
>