Search Postgresql Archives

Re: comparison between 2 execution plans

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx>:
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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux