Re: Avoiding duplication of code via views -- slower? How do people typically do this? - Mailing list pgsql-general

Joe Van Dyk 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?
>
> Thanks,
> Joe
>
>
 From your first example on the gist I extracted this. It should avoid
the multiple scans and hash join the the join of the two views suffers
from.

create view promotions_with_filters as (
   select *,
     end_at > now() - '30 days'::interval as recently_expired,
     quantity = 1 as one_time_use,
     exists(select 1 from promotion_usages pu on pu.promotion_id = p.id)
as used
   from promotions
);

select count(*) from promotions_with_filters where recently_expired and
one_time_use;