| From: | Neto pr <netoprbr9(at)gmail(dot)com> | 
|---|---|
| To: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> | 
| Subject: | comparison between 2 execution plans | 
| Date: | 2018-05-05 13:26:55 | 
| Message-ID: | CA+TZvYLiVT+7UBTiEZWPMS-r9Px28mYtvyGGx_R9nF84_O9Gag@mail.gmail.com | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-general | 
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Christof Priller | 2018-05-05 13:58:14 | User defined functions in Postgresql, troubles with the c code | 
| Previous Message | Pavel Stehule | 2018-05-05 12:13:36 | Re: How to find the hits on the databases and tables in Postgres |