Re: possible optimizations - pushing filter before aggregation - Mailing list pgsql-hackers

On Fri, Nov 18, 2016 at 12:47 AM Pavel Stehule <pavel.stehule@gmail.com> wrote:

Isn't possible in this case push equivalence before aggregation?

If I'm understanding you correctly, that would lead to wrong results. Here's a simple example:

CREATE VIEW v AS SELECT MIN(a) m FROM t;

and table T contains:

T:A

---

1

2

3

SELECT * FROM v WHERE m = 2

The minimum value of A is 1, so the query should return no rows.

However, if we filter first we'd be effectively doing the query:

SELECT MIN(a) m FROM

   (SELECT a FROM t WHERE a=2) AS v(a)

The subquery is going to return an intermediate result of:

V:A

---

2

And the minimum of that is 2, which is the wrong answer.

- Doug

Salesforce