On Thu, 16 Jan 2003, Roman Fail wrote:
> > Stephan Szabo wrote:
> > I'd assume that tranamount values are fairly randomly distributed
> > throughout the table, right? It takes about 5 minutes for the
> > system to read the entire table and more for the index scan, so
> > you're probably reading most of the table randomly and the index
> > as well.
> > What values on batchdetail do you use in query where clauses regularly?
> Yes, tranamount values are randomly distributed. I don't understand
> why an index scan would be "random", isn't the whole point of an index
> to have an ordered reference into the data? batchdetail has 5 columns
> that can be in the WHERE clause, all of which are indexed. None is
> more likely than the other to be searched, so a clustered index
> doesn't make much sense to me. The whole thing needs to be fast.
Yeah, in that case a clustered index doesn't help.
Indexes give you an ordered way to find the rows that meet a condition,
but say you had three rows in your table in this order (note that this is
an amazing oversimplification):
(1,'a')
(2,'b')
(0,'c')
And you want to scan the index from values with the first number between 0
and 2. It reads the third row, then the first, then the second (to get
the letter associated). Between those reads, it's got to seek back and
forth through the heap file and the order in which it hits them is pretty
random seeming (to the kernel).
> > Ron Johnson wrote:
> > What are the indexes on batchdetail?
> > There's one on batchid and a seperate one on tranamount?
> > If so, what about dropping them and create a single multi-segment
> > index on "batchid, tranamount". (A constraint can then enforce
> > uniqueness on batchid.
> There is no index on batchid, I think it is a good idea to create
> one. Stephan also suggested this. After I try the single batchid
> index, I might try to multi-segment index idea as well. I'll post
> results later today.
I think we may all have misread the index list to include an index on
batchid. Also you have two indexes on batchdetailid right now (primary key
also creates one) which added to the confusion.
> > Stephan Szabo wrote:
> > Then I realized that batchheader.batchid and
> > batchdetail.batchid don't even have the same
> > type, and that's probably something else you'd
> > need to fix.
>
> Yes, that's a mistake on my part....batchdetail(batchid) should be an
> int8. It looks to me like converting this datatype can't be done with
> a single ALTER TABLE ALTER COLUMN statement.....so I guess I'll work
> around it with an ADD, UPDATE, DROP, and RENAME.
Don't forget to do a vacuum full in there as well.