comparison between 2 execution plans

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

Responses

Browse pgsql-general by date

  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