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: | Raw Message | Whole Thread | 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 |