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;
$$;
The one thing is you'd probably have to manually write in your join against promotion_uses to make that effective, But you could instead do:
