On Fri, Feb 15, 2013 at 1:42 AM, Chris Travers <chris.travers@xxxxxxxxx> wrote: > On Thu, Feb 14, 2013 at 4:32 PM, Joe Van Dyk <joe@xxxxxxxxx> wrote: >> >> See >> https://gist.github.com/joevandyk/4957646/raw/86d55472ff8b5a4a6740d9c673d18a7005738467/gistfile1.txt >> for the code. >> >> I have promotions(id, end_at, quantity) and >> promotion_usages(promotion_id). >> >> I have a couple of things I typically want to retrieve, and I'd like those >> things to be composable. In this case, finding recently-expired promotions, >> finding promotions that have a quantity of one, and finding promotions that >> were used. >> >> My approach is to put these conditions into views, then I can join against >> each one. But that approach is much slower than inlining all the code. >> >> How is this typically done? > > > First I am not usually a fan of trying to reduce code duplication by using > views. In general, my experience is that this makes it very easy to make > things slower, and it adds unexpected optimization hedges in unexpected > places. > > Your problem here seems to be of this sort. You are joining together two > views in order to add filters. These operations are not really guaranteed > to be the same and so you have an unexpected optimization fence. > > My general rule of thumb is to consider moving inline views and WITH clauses > into views as needed. > > Now I think there are a bunch of ways to accomplish what you are trying to > do here. > > At the risk of jumping straight ahead into advanced functionality and the > accusations that I am making use of magic wands, I will suggest an > object-relational approach to reducing code duplication. This would be to > eliminate most your filter views and make use instead of table methods. > > CREATE FUNCTION recently_expired(promotion) returns bool language sql > immutable as > $$ > select $1.ended at > now() - '30 days'::interval; > $$; > > CREATE FUNCTION is_one_time(promotion) returns bool language sql immutable > as > $$ > select $1.quantity = 1; > $$; Unfortunately from performance point of view that is a much worse way to do things. Pushing checks into function like that forces processing into a iterative model which has a much worse set of performance gotchas that have essentially no workaround. This is because there is no way to force the function to be inlined. What I'd like to see is to have a new function decoration, INLINE, that introduces some constraints to how the function can be written and forces the function to be expanded in the query at plan time. Only then will there be a true alternative to using views, especially if you could inline through a LATERAL function call. merlin. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
