gistfile1.txt

root@127.160.87.35:34201/movr> create table employees(salary int, payroll_tax int, benefits_cost int, country text, title text);
CREATE TABLE

Time: 7.7314ms

root@127.160.87.35:34201/movr> explain
                            -> SELECT
                            ->     title,
                            ->     country,
                            ->     AVG(salary) AS average_salary,
                            ->     SUM(salary) AS sum_salary,
                            ->     AVG(salary + payroll_tax) AS average_gross_salary,
                            ->     SUM(salary + payroll_tax) AS sum_gross_salary,
                            ->     AVG(salary + payroll_tax + benefits_cost) AS average_gross_cost,
                            ->     SUM(salary + payroll_tax + benefits_cost) AS sum_gross_cost
                            -> FROM employees
                            -> WHERE salary + payroll_tax + benefits_cost > 0 AND country = 'USA'
                            -> GROUP BY title, country
                            -> HAVING count(*) > 200
                            -> ORDER BY sum_gross_cost
                            -> LIMIT 20;
                 tree                |    field    |                             description
-------------------------------------+-------------+-----------------------------------------------------------------------
                                     | distributed | true
                                     | vectorized  | false
  render                             |             |
   └── limit                         |             |
        │                            | count       | 20
        └── sort                     |             |
             │                       | order       | +sum
             └── filter              |             |
                  │                  | filter      | count_rows > 200
                  └── group          |             |
                       │             | aggregate 0 | title
                       │             | aggregate 1 | avg(salary)
                       │             | aggregate 2 | sum(salary)
                       │             | aggregate 3 | avg(column9)
                       │             | aggregate 4 | sum(column9)
                       │             | aggregate 5 | avg(column12)
                       │             | aggregate 6 | sum(column12)
                       │             | aggregate 7 | count_rows()
                       │             | aggregate 8 | any_not_null(country)
                       │             | group by    | title
                       └── render    |             |
                            └── scan |             |
                                     | table       | employees@primary
                                     | spans       | FULL SCAN
                                     | filter      | ((benefits_cost + (salary + payroll_tax)) > 0) AND (country = 'USA')
(25 rows)

Time: 7.47456ms

root@127.160.87.35:34201/movr> explain
                            -> select *
                            -> from
                            -> (
                            ->     select *
                            ->     from
                            ->     (
                            ->         select
                            ->         AVG(salary) AS average_salary,
                            ->         SUM(salary) AS sum_salary,
                            ->         AVG(salary + payroll_tax) AS average_gross_salary,
                            ->         SUM(salary + payroll_tax) AS sum_gross_salary,
                            ->         AVG(salary + payroll_tax + benefits_cost) AS average_gross_cost,
                            ->         SUM(salary + payroll_tax + benefits_cost) AS sum_gross_cost,
                            ->         COUNT(*) as count
                            ->         from
                            ->         (
                            ->             select *
                            ->             from
                            ->             (
                            ->                 select *
                            ->                 from
                            ->                 (
                            ->                     select gross_salary + benefits_cost as gross_cost, *
                            ->                     from
                            ->                     (
                            ->                         select salary + payroll_tax as gross_salary,  *
                            ->                         from
                            ->                         (
                            ->                             select *
                            ->                             from
                            ->                             (
                            ->                                 select *
                            ->                                 from employees
                            ->                             ) as tmp
                            ->                             where country = 'USA'
                            ->                         ) as tmp
                            ->                     ) as tmp
                            ->                 ) as tmp
                            ->                 where gross_cost > 0
                            ->             ) as tmp
                            ->         ) as tmp
                            ->         GROUP BY title, country
                            ->     ) as tmp
                            ->     where count > 200
                            -> ) as tmp
                            -> order by sum_gross_cost
                            -> limit 20;
                 tree                |    field    |                             description
-------------------------------------+-------------+-----------------------------------------------------------------------
                                     | distributed | true
                                     | vectorized  | false
  render                             |             |
   └── limit                         |             |
        │                            | count       | 20
        └── sort                     |             |
             │                       | order       | +sum
             └── filter              |             |
                  │                  | filter      | count_rows > 200
                  └── group          |             |
                       │             | aggregate 0 | title
                       │             | aggregate 1 | avg(salary)
                       │             | aggregate 2 | sum(salary)
                       │             | aggregate 3 | avg(column11)
                       │             | aggregate 4 | sum(column11)
                       │             | aggregate 5 | avg(column14)
                       │             | aggregate 6 | sum(column14)
                       │             | aggregate 7 | count_rows()
                       │             | group by    | title
                       └── render    |             |
                            └── scan |             |
                                     | table       | employees@primary
                                     | spans       | FULL SCAN
                                     | filter      | (country = 'USA') AND ((benefits_cost + (salary + payroll_tax)) > 0)
(24 rows)

Time: 2.971614ms