Re: window function induces full table scan

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Thomas Mayer <thomas(dot)mayer(at)student(dot)kit(dot)edu>
Cc: pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: window function induces full table scan
Date: 2014-01-02 21:52:50
Message-ID: 17191.1388699570@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Thomas Mayer <thomas(dot)mayer(at)student(dot)kit(dot)edu> 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 condition is applied.
> This induces a full table scan.

You haven't exactly provided full details, but it looks like you are
thinking that WHERE clauses applied above a window function should
be pushed to below it. A moment's thought about the semantics should
convince you that such an optimization would be incorrect: the window
function would see fewer input rows than it should, and therefore would
(in general) return the wrong values for the selected rows.

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
window functions to determine that.

regards, tom lane

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Jeff Janes 2014-01-02 22:26:31 Re: window function induces full table scan
Previous Message Thomas Mayer 2014-01-02 21:32:01 window function induces full table scan