From: | Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | left join with smaller table or index on (XXX is not null) to avoid upsert |
Date: | 2009-01-18 21:12:07 |
Message-ID: | 20090118221207.760eb176@dawn.webthatworks.it |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I've to apply a discounts to products.
For each promotion I've a query that select a list of products and
should apply a discount.
Queries may have intersections, in these intersections the highest
discount should be applied.
Since queries may be slow I decided to proxy the discount this way:
create table Product(
ProductID int primary key,
ListPrice numeric
);
create table ProductPrice(
ProductID int references Products (ProcuctID),
DiscountedPrice numeric
);
Case A)
If I want the ProductPrice to contain just products with a
discount I'll have to update, then see if the update was successful
otherwise insert.
I expect that the products involved may be around 10% of the overall
products.
To retrieve a list of products I could:
select [some columns from Product],
least(coalesce(p.ListPrice,0),
coalesce(pp.DiscountedPrice,0)) as Price
from Product
left join ProductPrice pp on p.ProductID=pp.ProductID
where [some conditions on Product table];
create index ProductDiscount_ProductID_idx on DiscountPrice
(ProductID);
Case B)
Or ProductPrice may just contain ALL the products and everything
will be managed with updates.
select [some columns from Product],
least(coalesce(p.ListPrice,0),
coalesce(pd.DiscountedPrice,0))
from Product
left join ProductDiscount pd on p.ProductID=pd.ProductID and
pd.DiscountPrice is not null
where [some conditions on Product table];
create index ProductDiscount_DiscountedPrice_idx on DiscountPrice
(DiscountPrice is not null);
create index ProductDiscount_ProductID_idx on DiscountPrice
(ProductID);
I'm expecting that:
- ProductPrice will contain roughly but less than 10% of the
catalogue.
- I may have from 0 to 60% overlap on queries generating the list of
products to be discounted.
- The overall number of promotions/query running concurrently may be
in the range of 20-100
- promotions will be created/deletes at a rate of 5-10 a day, so
that discount will have to be recalculated
- searches in the catalogue have to be fast
Since I haven't been able to find a quick way to build up a
hierarchy of promotions to apply/re-apply discounts when promotion
are added/deleted, creating/deleting promotions looks critical as
well.
The best thing I was able to plan was just to reapply all promotions
if one is deleted.
So it looks to me that approach B is going to make updating of
discounts easier, but I was wondering if it makes retrieval of
Products and Prices slower.
Having a larger table that is being updated at a rate of 5% to 10% a
day may make it a bit "fragmented".
Advices on the overall problem of discount overlap management will
be appreciated too.
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
From | Date | Subject | |
---|---|---|---|
Next Message | A B | 2009-01-18 21:43:43 | Is this on the to-do list? |
Previous Message | Tom Lane | 2009-01-18 16:43:03 | Re: invalid value for parameter "lc_messages": "en_US.ISO8859-1" |