----- Original Message ----- From: "Curt Sampson" <[EMAIL PROTECTED]> To: "Donny Drummonds" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, February 07, 2003 5:13 AM Subject: Re: [ADMIN] Indexes on Large Tables
> On Mon, 3 Feb 2003, Donny Drummonds wrote: > > > If I do not index the column from the where clause the query returns > > the 150,000 rows in 4 and a half minutes. If in do index the column > > from the where clause using a btree the 150,000 rows return in 11 and > > a half minutes. > > > > Any insight would be greatly appreciated. > > Well, I'm not sure if this is the insight you're looking for, but.... > > The reason it takes longer if you use an index is that you change > from sequential I/O (which is relatively fast) to random I/O (which > is relatively slow). With the table scan (reading the entire table in > whatever order it's in on the disk) you're reading several times as much > data, but you're not doing head seeks all over the place to move the > head to the place where the next bit of data to be read is. > > Obviously, in this case, even though an index was available, the planner > was wrong to chose to use it rather than just read the entire table. > That is, as someone else mentioned, likely due to bad statistics: the > planner thought you were going to select a very small part of the table, > rather than ten percent of it (which is a pretty large fraction, for > these purposes). Try doing an ANALYZE. > > cjs If the index that you use in the WHERE clause is the most common method that you are accessing the table then I recommend that you periodically run the cluster command: CLUSTER indexname ON tablename When a table is clustered, it is physically reordered on disk based on the index information. Donald ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
