Re: comparison between 2 execution plans

On 05/05/2018 10:51 AM, Neto pr wrote:
> Dear,
>
>
> 2018-05-05 9:57 GMT-07:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>:
>
> On 05/05/2018 06:26 AM, Neto pr wrote:

>
> It might help if you explained what 'version with source code
> changed by me' means?
>
>
> Postgresql with modified source code, is that I modified some internal
> functions of cost (source code) and parameters in Postgresql.conf so
> that it is possible for the DBMS to differentiate cost of read (random
> and sequence) and write (random and sequence), this is because reading
> in SSDs' and more than 400 times faster than HDD. This is due to
> academic research that I am doing.

Seems to me that the above may enter into the difference between
instances. Someone with more knowledge of the Postgres internals then I
would need to comment, contingent on seeing the changes I would presume.

Also from my previous post:

Any reason why the index changed between instances?

>
>
> Also the schema for the table lineitem from both instances might
> help shed light.
>
>
> see schema of the tables below:
> https://docs.snowflake.net/manuals/_images/sample-data-tpch-schema.png
>
> I am using 40g scale, in this way the lineitem table has (40 * 6
> million) 240 million of the rows.
>
>
> Any reason why the index changed between instances?
>
>
>
> 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
>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>
>
> Regards
> Neto

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com