On Thu, Feb 14, 2013 at 4:32 PM, Joe Van Dyk <joe@tanga.com> wrote:
> See
> https://gist.github.com/joevandyk/4957646/raw/86d55472ff8b5a4a6740d9c673d18a7005738467/gistfile1.txtfor 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:
select p.id from promotions p join promotion_usages pu on pu.promotion_id = p.id
where p.is_one_time and p.recently_expired;
