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;
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
