On Fri, Mar 1, 2013 at 1:21 AM, Raymond O'Donnell <r...@iol.ie> wrote:
> On 01/03/2013 00:19, Chris Hanks wrote: > > On Thu, Feb 28, 2013 at 6:00 AM, Tom Lane <t...@sss.pgh.pa.us > > <mailto:t...@sss.pgh.pa.us>> wrote: > > > > Chris Hanks <christopher.m.ha...@gmail.com > > <mailto:christopher.m.ha...@gmail.com>> writes: > > > create or replace view values_view as > > > select fkey1, fkey3, > > > (derived1 / max(derived1) over (partition by fkey1)) as derived1, > > > (derived2 / sum(derived1) over (partition by fkey1)) as derived2 > > > from ( > > > select fkey1, fkey3, > > > cast(sum((case when (value > 0.0) then 4 else 1 end)) as double > > > precision) as derived1, > > > sum((case when (value > 0.0) then (value * 4) else (value + 1) > > end)) as > > > derived2 > > > from values > > > group by fkey1, fkey3 > > > ) as t1; > > > > > -- This query requires a sequential scan on values, though all the > > data it > > > needs could be found much more efficiently with an index scan. > > > explain analyze select * from values_view where fkey1 = 1263; > > > > To use the outer WHERE clause as an index constraint, postgres would > > have to prove that scanning only the rows with fkey1 = 1263 would > still > > find all the rows that would get examined by the window functions --- > > and in this case, it's not only the window functions that make that > less > > than obvious, but the grouped aggregates in the sub-select below > them. > > There's not nearly that amount of intelligence in the system about > > window functions, as yet. So you'll have to write out the query > > longhand and put the WHERE clause at the lower level, if you want > this > > optimization to happen. > > > > regards, tom lane > > > > > > Ok, that makes sense, thanks. > > > > Can anyone point me to an example of wrapping a function in a view, like > > Merlin suggested? I'm not sure how that would work. > > Off the top of my head, I'd imagine it's as simple as: > > create view ... as > select * from my_function(...); > > :-) > > Ray. > > > -- > Raymond O'Donnell :: Galway :: Ireland > r...@iol.ie > Sorry, I don't understand. I'm able to make a function that takes an integer and uses it in the subselect as "WHERE fkey1 = arg", and that works as I expect it to and it's plenty fast. But I don't see how to write a view to take advantage of this function - what arguments would go in my_function(...) when I'm declaring the view? Chris
