Re: Questions about my strategy

On Mon, 2002-07-29 at 22:49, Rob Brown-Bayliss wrote:

> The problem as I see it is after a length of time the table will reach a
> large size and then getting counts of stock on hand will become quite
> slow, so I plan to have a stock take date in the system, and then limit
> it to all rows after the last stocktake.
>
> This is guess will require an index on teh timestamp column.
>

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?

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.

Also, I'm sure there are more knowledgable folks round here who may have
deeper insights that they can offer.

Regards

John

[*] Sequential scan is faster than index scan for large setsb being
returned because a) it exploits the readahead behaviour of your OS and
b) the tuple visibility information is stored in the heap (the main
table) and has to be looked up anyway (though this won't really cause a
problem if you rarely/never UPDATE the table) and c) when seen via the
index, the heap will also be in random order. Apologies if you knew this
all already.

--
John Gray
Azuli IT
www.azuli.co.uk