On 01/03/2013 00:19, Chris Hanks wrote: > On Thu, Feb 28, 2013 at 6:00 AM, Tom Lane <tgl@xxxxxxxxxxxxx > <mailto:tgl@xxxxxxxxxxxxx>> wrote: > > Chris Hanks <christopher.m.hanks@xxxxxxxxx > <mailto:christopher.m.hanks@xxxxxxxxx>> 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 rod@xxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
