Skip site navigation (1) Skip section navigation (2)

Re: Questions about my strategy

From: Rob Brown-Bayliss <rob(at)zoism(dot)org>
To: John Gray <jgray(at)azuli(dot)co(dot)uk>
Cc: PostgreSQL General List <pgsql-general(at)postgresql(dot)org>
Subject: Re: Questions about my strategy
Date: 2002-07-30 00:20:20
Message-ID: 1027988419.3063.7.camel@everglade.zoism.org (view raw or flat)
Thread:
Lists: pgsql-general
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
*

In response to

pgsql-general by date

Next:From: Sanjeev RathoreDate: 2002-07-30 01:11:03
Subject: insert bytea type into PostgreSQL database
Previous:From: Bruce MomjianDate: 2002-07-29 22:20:35
Subject: Re: B-trees (Indexes) storage space

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group