Re: Questions about my strategy
От | John Gray |
---|---|
Тема | Re: Questions about my strategy |
Дата | |
Msg-id | 1027981176.1740.103.camel@adzuki обсуждение исходный текст |
Ответ на | Questions about my strategy (Rob Brown-Bayliss <rob@zoism.org>) |
Ответы |
Re: Questions about my strategy
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: