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

From Pavel Stehule
Subject Re: possible optimizations - pushing filter before aggregation
Date
Msg-id CAFj8pRAgfM+7sdyHL5gfhPStu9C3TZH5ZRizCvKaVUaas9GjUQ@mail.gmail.com
Whole thread Raw
In response to Re: possible optimizations - pushing filter before aggregation  (Douglas Doole <dougdoole@gmail.com>)
Responses Re: possible optimizations - pushing filter before aggregation
List pgsql-hackers


2016-11-19 3:59 GMT+01:00 Douglas Doole <dougdoole@gmail.com>:

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.

yes, you have true,

thank you for correcting

Regards

Pavel
 

- Doug
Salesforce

pgsql-hackers by date:

Previous
From: Andrew Dunstan
Date:
Subject: Re: Mail thread references in commits
Next
From: Pavel Stehule
Date:
Subject: Re: patch: function xmltable