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