On Thu, Feb 14, 2013 at 6:31 PM, Jack Christensen
<jack@jackchristensen.com>wrote:
> Joe Van Dyk wrote:
>
>> See https://gist.github.com/**joevandyk/4957646/raw/**
>>
86d55472ff8b5a4a6740d9c673d18a**7005738467/gistfile1.txt<https://gist.github.com/joevandyk/4957646/raw/86d55472ff8b5a4a6740d9c673d18a7005738467/gistfile1.txt>for
thecode.
>>
>> 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;
>
Perhaps I fat-fingered something somewhere... I tried that and I got this:
https://gist.github.com/joevandyk/4958906/raw/5561f95ef2b5d82f81ab14913c4d36f6aac3ee0a/gistfile1.txt
The with_filters view uses a different plan.
