Re: Questions about my strategy

On Tue, 2002-07-30 at 10:19, John Gray wrote:

> Bear in mind that the PostgreSQL query optimiser is unlikely to use an
> index if more than a few percent of the rows will be returned. If the
> tuning parameters are set correctly, the index lookup should kick in
> only when it would be faster[*]. If your stocktakes are equally
> distributed amongst your transactions, then I suspect there would have
> to be about 30 stocktakes (i.e. transactions partioned into about 30
> sets) before the index would be valuable. How frequent are stocktake
> entries going to be compared to transactions?

Basically they wont, after the stock has been counted a single
transaction will be entered for each product, the count column being a
correction, ie count = -4 if there are 4 items less tha nthere should
be, 3 if more or 0 if the same. then the stocktake timestamp will be
created and from then when looking up how many items in stock I will
ignore all transactions older than the stocktake timestamp.

> Of course, the only harm in creating an index is that it will slow
> inserts down slightly. There have also been some suggestions that the
> default btree index implementation in PG is not so efficient with
> continuously increasing keys e.g. timestamps.

I would like to hear more about index types, also what about clustering,
I havn't readinto it yet, but understand it orders a table by the index,
but then items inserted with a time stamp of the inestion will be
automaitacally cluseterd by their time stamp wont they? (assuming I neve
delete or update any rows)

--

*
* Rob Brown-Bayliss
*