grouping-sets-pushdown-bug.sql

/*
Using GROUPING SETS with more than one set disables predicate pushdown?

Version:
PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit


Seems like when GROUPING SETS with at least two sets are used in the subquery, planner
can not push WHERE clauses inside.

Here are two queries that (I think) are equivalent, but produce very different execution
plans leading to bad performance on real data - and in effect,
making it impossible to abstract away non-trivial grouping logic into a view.

It might as well be that queries are not really equivalent, but I don't see how.

Same problem happens even if grouping sets are the same - like `GROUPING SETS ((), ())`.
*/

CREATE TEMPORARY TABLE test_gs (
    x INT,
    y INT,
    z INT,
    PRIMARY KEY (x, y, z)
);

EXPLAIN
SELECT
    x,
    y,
    avg(z) AS mean
FROM test_gs
WHERE x = 1
GROUP BY x, GROUPING SETS ((y), ());
/*
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 GroupAggregate  (cost=0.15..8.65 rows=20 width=40)
   Group Key: x, y
   Group Key: x
   ->  Index Only Scan using test_gs_pkey on test_gs  (cost=0.15..8.33 rows=10 width=12)
         Index Cond: (x = 1)
(5 rows)

*/


EXPLAIN
SELECT x, y, mean
FROM (
         SELECT
             x,
             y,
             avg(z) AS mean
         FROM test_gs
         GROUP BY x, GROUPING SETS ((y), ())
     ) AS g
WHERE x = 1;
/*
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 GroupAggregate  (cost=0.15..62.10 rows=404 width=40)
   Group Key: test_gs.x, test_gs.y
   Group Key: test_gs.x
   Filter: (test_gs.x = 1)
   ->  Index Only Scan using test_gs_pkey on test_gs  (cost=0.15..41.75 rows=2040 width=12)
(5 rows)

*/