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.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 
> 
> Regards
> Neto


-- 
Adrian Klaver
adrian.klaver@aklaver.com