On Fri, Dec 20, 2013 at 10:46 PM, David Rowley wrote:
> On Fri, Dec 20, 2013 at 1:35 PM, Dave Johansen wrote:
>
>> I just ran into an interesting issue on Postgres 8.4. I have a database
>> with about 3 months of data and when I do following query:
>> SELECT DATE_TRUNC('day', time) AS time_t, COU
On Thu, Jan 2, 2014 at 11:39 AM, Dave Johansen wrote:
> On Fri, Dec 20, 2013 at 10:46 PM, David Rowley wrote:
>
>> On Fri, Dec 20, 2013 at 1:35 PM, Dave Johansen wrote:
>>
>>> I just ran into an interesting issue on Postgres 8.4. I have a database
>>> with about 3 months of data and when I do foll
On Thu, Jan 2, 2014 at 12:36 PM, Dave Johansen wrote:
> On Thu, Jan 2, 2014 at 11:39 AM, Dave Johansen wrote:
>
>> On Fri, Dec 20, 2013 at 10:46 PM, David Rowley wrote:
>>
>>> On Fri, Dec 20, 2013 at 1:35 PM, Dave Johansen
>>> wrote:
>>>
I just ran into an interesting issue on Postgres 8.4.
When querying a view with a WHERE condition, postgresql normally is able
to perform an index scan which reduces time for evaluation dramatically.
However, if a window function is evaluated in the view, postgresql is
evaluating the window function before the WHERE condition is applied.
This ind
Thomas Mayer writes:
> When querying a view with a WHERE condition, postgresql normally is able
> to perform an index scan which reduces time for evaluation dramatically.
> However, if a window function is evaluated in the view, postgresql is
> evaluating the window function before the WHERE co
On Thu, Jan 2, 2014 at 1:52 PM, Tom Lane wrote:
> Thomas Mayer writes:
> > When querying a view with a WHERE condition, postgresql normally is able
> > to perform an index scan which reduces time for evaluation dramatically.
>
> > However, if a window function is evaluated in the view, postgresq
Jeff Janes writes:
> On Thu, Jan 2, 2014 at 1:52 PM, Tom Lane wrote:
>> It's possible that in the specific case you exhibit here, pushing down
>> the clause wouldn't result in changes in the window function's output for
>> the selected rows, but the optimizer doesn't have enough knowledge about
>
You understood me correctly, Tom.
As you mention, the result would be correct in my case:
- The window function is performing a "PARTITION BY user_id".
- user_id is used for the WHERE condition.
I agree, that in general (PARTITION BY and WHERE don't use the same set
of attributes), incorrect re
Am 02.01.2014 23:43, schrieb Tom Lane:
Jeff Janes writes:
On Thu, Jan 2, 2014 at 1:52 PM, Tom Lane wrote:
It's possible that in the specific case you exhibit here, pushing down
the clause wouldn't result in changes in the window function's output for
the selected rows, but the optimizer does
I wrote:
> If the restriction clause must give the same answer for any two rows of
> the same partition, then yeah, we could in principle push it down without
> knowing anything about the specific window function. It'd be a less than
> trivial test to make, I think.
On reflection, really this con
Just to track it down: The limitation can also be reproduced without
using views. Using views is just a use case where the suggested
optimization is actually needed.
Plus, when I remove the condition "WHERE datepos = 1", the same
behaviour still occurs. Here, I wanted to see if postgresql is
I have just cloned the postgresql git repository and checked out the
REL9_3_2 tagged version to have a look at the
src/backend/optimizer/path/allpaths.c file.
As Tom already mentioned, quals are currently not pushed down when
subqueries with window functions occur:
There is a function subque
12 matches
Mail list logo
