Search Postgresql Archives

How do query optimizers affect window functions

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi, Postgresql,

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;

The expected results are:

 sum | unique1 | four
-----+---------+------
  45 |       4 |    0
  41 |       2 |    2
  39 |       1 |    1
  38 |       6 |    2
  32 |       9 |    1
  23 |       8 |    0
  15 |       5 |    1
  10 |       3 |    3
   7 |       7 |    3
   0 |       0 |    0

But the real results are:

 sum | unique1 | four
-----+---------+------
  45 |       0 |    0   
  45 |       1 |    1   
  44 |       2 |    2   
  42 |       3 |    3   
  39 |       4 |    0   
  35 |       5 |    1   
  30 |       6 |    2   
  24 |       7 |    3   
  17 |       8 |    0   
   9 |       9 |    1   

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?

Thanks a lot!
Tianyin
 

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux