I want to understand how the query optimizers affect the output of the window functions.
For example, set "cpu_tuple_cost = 50" in postgresql.conf and start the server, I apply the regress test (make installcheck). The test of window function fails.
Checking the diff and I found the output of the window functions are different. For example,
For the following query:
SELECT sum(unique1) over (rows between current row and unbounded following), unique1, four FROM tenk1 WHERE unique1 < 10;
There're altogether 6 queries in window test that outputs different query results.
I don't understand why the results are different. Intuitively, the queries show return the same results no matter what plan the optimizer choose.
I suspected the previous queries had some side effect on the latter one (e.g., change the current row), so I removed all the previous queries before this query in window.sql. But the result did not change.
Could anyone explain this behavior? Or point out how to investigate?