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

Re: Index behavior question

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Dmitri Touretsky <dmitri(at)listsoft(dot)ru>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Index behavior question
Date: 2003-03-30 19:24:58
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
Dmitri Touretsky <dmitri(at)listsoft(dot)ru> writes:
> In the docs it's written that index changes every time update is done
> to the table. But if I update the field which is not indexed - will
> postgres spend much time on changing indexes?

It doesn't matter which columns you update.  Any UPDATE writes a new
version of the row, which requires all its own index entries.  The old
row version and its index entries stay around until VACUUM reclaims
them.  You can read more about this in the "concurrency control" chapter
of the manual.

> The idea is that I have a large table with many indexes defined. The
> table has much more selects, than updates or inserts. But in this
> table there are few fields which do not require indexing, but needs to
> be updates relatively often. Yet I want to keep selects as fast as
> possible. And now I'm thinking if it worth to create a separate table
> with foreign key defined for those fields...

I think this would only win if most of your selects didn't need to
access the columns you'd moved out to a separate table.  Otherwise the
cost of joining will slow down the selects more than you want.

			regards, tom lane

In response to

pgsql-novice by date

Next:From: Tom LaneDate: 2003-03-30 19:42:47
Subject: Re: Modifying WHERE input conditions for a VIEW
Previous:From: Tony HolmesDate: 2003-03-30 19:15:07
Subject: Modifying WHERE input conditions for a VIEW

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