Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux