Postgres Pro
Downloads
Home   >   mailing lists

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

From Douglas Doole
Subject Re: possible optimizations - pushing filter before aggregation
Date
Msg-id CADE5jY+izr7SMGVDmhEr6ksRPnYRPOQ_=mXTLyQnT+bsL+T32g@mail.gmail.com
Whole thread Raw
In response to possible optimizations - pushing filter before aggregation  (Pavel Stehule <pavel.stehule@gmail.com>)
Responses Re: possible optimizations - pushing filter before aggregation
List pgsql-hackers
Tree view

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

pgsql-hackers by date:

Previous
From: Michael Paquier
Date:
Subject: Re: Mail thread references in commits
Next
From: Amit Kapila
Date:
Subject: Re: Fix checkpoint skip logic on idle systems by tracking LSN progress