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

We have an FAQ item about this, 4.8.

---------------------------------------------------------------------------

Jonathan Bartlett wrote:
> 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/
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073