Thalis Kalfigkopoulos <tkalfigo@xxxxxxxxx> writes: > # SELECT id, experiment, first_value(insertedon) OVER (PARTITION BY score, > id) AS first_insertedon, score FROM data WHERE id=1160; > [ versus ] > # CREATE VIEW clustered_view AS SELECT id, experiment, > first_value(insertedon) OVER (PARTITION BY score, id) AS first_insertedon, > score FROM data; > # SELECT * from clustered_view WHERE id=1160; One possible reason these produce different results is in that in the first case, the WHERE condition eliminates rows from the window function's consideration. In the second case, it doesn't --- the WHERE only filters the result rows from the view. However, the fact that "id" is part of the partition list may insulate you from that; not quite sure without seeing a more complete example. Another likely reason for trouble is that the window function seems underspecified: without any ORDER BY clause, you are going to get a random one of the insertedon values for the same score and id. It's entirely likely that moving the WHERE clause would change the plan enough to change the ordering of the rows seen by the window function. Possibly you should be using min() instead of first_value(). regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
