On Thu, Feb 14, 2013 at 6:31 PM, Jack Christensen <jack@xxxxxxxxxxxxxxxxxxx> wrote:
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;
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.
