From: | Neto pr <netopr9(at)gmail(dot)com> |
---|---|
To: | postgres performance list <pgsql-performance(at)postgresql(dot)org> |
Subject: | help in analysis of execution plans |
Date: | 2018-05-05 15:16:42 |
Message-ID: | CA+wPC0PD7WfFPUygZwDRL1bGxeukPyzt15bGRnSbfTPi2Um93w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Neto pr | 2018-05-05 18:07:26 | Re: help in analysis of execution plans |
Previous Message | Alf Lervåg | 2018-04-30 19:43:19 | Why doesn't the second query use the index for sorting? |