opt: derived projections shouldn't be pushed past sorts

[jordanlewis]

Here's the plan for TPCH Q1:

root@:26257/tpch> explain(verbose) select
sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice * (l_discount - 1)) as sum_disc_price, 
sum(l_extendedprice * (l_discount -1) * (1 + l_tax)) as sum_charge, 
avg(l_quantity) as avg_qty,   
avg(l_extendedprice) as avg_price, 
avg(l_discount) as avg_disc
from lineitem                                                                                                                                                                                               group by l_returnflag, l_linestatus                                                                                                                                                                                           order by l_returnflag,  l_linestatus;
            tree           |    field    |                      description                       |                                          columns                                          |          ordering
+--------------------------+-------------+--------------------------------------------------------+-------------------------------------------------------------------------------------------+-----------------------------+
  render                   |             |                                                        | (sum_qty, sum_base_price, sum_disc_price, sum_charge, avg_qty, avg_price, avg_disc)       |
   │                       | render 0    | agg0                                                   |                                                                                           |
   │                       | render 1    | agg1                                                   |                                                                                           |
   │                       | render 2    | agg2                                                   |                                                                                           |
   │                       | render 3    | agg3                                                   |                                                                                           |
   │                       | render 4    | agg4                                                   |                                                                                           |
   │                       | render 5    | agg5                                                   |                                                                                           |
   │                       | render 6    | agg6                                                   |                                                                                           |
   └── group               |             |                                                        | (l_returnflag, l_linestatus, agg0, agg1, agg2, agg3, agg4, agg5, agg6)                    | +l_returnflag,+l_linestatus
        │                  | aggregate 0 | l_returnflag                                           |                                                                                           |
        │                  | aggregate 1 | l_linestatus                                           |                                                                                           |
        │                  | aggregate 2 | sum(l_quantity)                                        |                                                                                           |
        │                  | aggregate 3 | sum(l_extendedprice)                                   |                                                                                           |
        │                  | aggregate 4 | sum(column19)                                          |                                                                                           |
        │                  | aggregate 5 | sum(column21)                                          |                                                                                           |
        │                  | aggregate 6 | avg(l_quantity)                                        |                                                                                           |
        │                  | aggregate 7 | avg(l_extendedprice)                                   |                                                                                           |
        │                  | aggregate 8 | avg(l_discount)                                        |                                                                                           |
        │                  | group by    | @6,@7                                                  |                                                                                           |
        │                  | ordered     | @6,@7                                                  |                                                                                           |
        └── sort           |             |                                                        | (column19, column21, l_quantity, l_extendedprice, l_discount, l_returnflag, l_linestatus) | +l_returnflag,+l_linestatus
             │             | order       | +l_returnflag,+l_linestatus                            |                                                                                           |
             └── render    |             |                                                        | (column19, column21, l_quantity, l_extendedprice, l_discount, l_returnflag, l_linestatus) |
                  │        | render 0    | l_extendedprice * (l_discount - 1.0)                   |                                                                                           |
                  │        | render 1    | (l_extendedprice * (l_discount - 1.0)) * (l_tax + 1.0) |                                                                                           |
                  │        | render 2    | l_quantity                                             |                                                                                           |
                  │        | render 3    | l_extendedprice                                        |                                                                                           |
                  │        | render 4    | l_discount                                             |                                                                                           |
                  │        | render 5    | l_returnflag                                           |                                                                                           |
                  │        | render 6    | l_linestatus                                           |                                                                                           |
                  └── scan |             |                                                        | (l_quantity, l_extendedprice, l_discount, l_tax, l_returnflag, l_linestatus)              |
                           | table       | lineitem@primary                                       |                                                                                           |
                           | spans       | ALL                                                    |                                                                                           |
(33 rows)

Time: 2.552ms

As you can see, the optimizer pushes the "derived" render expressions (projections that aren't part of the scan) past the sort. This is inefficient, as it requires that the sort shuffle more memory.

Ideally, the optimizer should not materialize any extra projections below a sort, to avoid this kind of shuffling.

[RaduBerinde]

Sounds like we should be taking the number of total columns into account for sort.

[RaduBerinde]

Also, I wondered many times if it would make sense to swap the order in which we evaluate things, specifically to do enforcers last. I believe it might help in practice, even if the right answer is always that if a plan is better than another, the cost should reflect that.

[andy-kimball]

I think the coster just needs a good overhaul. It's still very simplistic, and so we see these kinds of anomalies. I'm planning to look into it more when I work on incorporating latencies into the cost function.