Re: comparison between 2 execution plans

On 05/05/2018 06:26 AM, Neto pr wrote:

Dear all

Could you help me understand these two execution plans for the same query (query 3 benchmark TPCH www.tpc.org/tpch <http://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

It might help if you explained what 'version with source code changed by me' means?

Also the schema for the table lineitem from both instances might help shed light.

Any reason why the index changed between instances?

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

--
Adrian Klaver
adrian.klaver@aklaver.com