Re: Why no performance boost although I added an index?

The problem is that if you are iterating over a large portion of your
dataset using an index, your disk has to do this:

Seek to the next index entry

Seek to the corresponding table entry

Seek to the next index entry

Seek to the corresponding table entry

...

If you get a lot of that, your disk will spend more time seeking
back-and-forth than actually reading data. Imagine if you used a CD
player and were constantly switching back between track 5 and track 9 of
the CD every 2 seconds - you would spend more time seeking than listening.

With two disks, you have one read-write head on the index, and one on the
table, so although they may be jumping around a little in the file, they
aren't moving nearly as much as if they are on the same disk. Buffering
may take the jumps out completely.

For small index scans, it makes little difference. For large index scans,
having the index on the same drive can really kill you.

To put them on different disks, you have to do the following:

Find the OID of the entity you wish to move.
Stop the database.
Move the entity to wherever you want it.
Symlink it to the original location.

The problems with this method are that:

a) The DB has to be down to do it

b) The symlink gets destroyed if you rebuild the index or cluster the
table.

Having said all that, I must say that I don't normally do this to my
databases, I just keep myself aware of the potential problems.

Jon

On Mon, 7 Apr 2003, Dan Langille wrote:

> On 7 Apr 2003 at 15:05, Jonathan Bartlett wrote:
>
> > This is a really big lose if your index and table are on the same
> > disk.
>
> Can you quantity "really big" for us please? Not all of us have more
> than one disk per box. I haven't looked into the ability for PG to
> put tables and indexes on specific disks.
> --
> Dan Langille : http://www.langille.org/
>