Re: left join with smaller table or index on (XXX is not null) to avoid upsert

On Sun, 18 Jan 2009 22:12:07 +0100
Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> wrote:

> 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:

Actually:
premature optimization is the root of all evil (Knuth).

Although I haven't reached any definitive conclusion clean design
and normalisation seem paid off.

A normal query to retrieve a list of products seems nearly
unaffected by keeping a

create table Promo (
PromoID serial primary key,
PromoStart timestamp,
PromoEnd timestamp,
..);
and a
create table PromoItem(
PromoID int references Promo (PromoID) on delete cascade,
ItemID int references Product (ProductID) on delete cascade,
Discount numeric(4,2) not null
);

and looking for max discount in a join on the fly.
That's on a 1M items and on 40K products on promo.
Distribution of promo was random, I'll dig further to get an idea of
worst case.
What's important is that a simple search over the catalogue takes
nearly the same time that a query that search through the catalogue
and find the appropriate discount.

Thanks to Knuth and to Postgresql coders.

I'll post a more detailed solution as soon as it's enough refined
and if I'm sure of its correctness.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it