Re: UPDATE and Indexes and Performance

On Wed, Oct 15, 2008 at 10:42 AM, Bill Thoen <bthoen(at)gisnet(dot)com> wrote:
> Does PG (8.1) ever use existing indexes when executing an UPDATE?
>
> I've got some tables with millions of records and whenever I update a column
> that involves most or all the records the EXPLAIN command seems to indicate
> that it isn't using the pre-existing indexes. This result in a slow update,
> which is further slowed by the presence of indexes. So when doing a large
> update should I just drop the indexes first, or is there some good reason to
> keep them?
>
> Thanks,
> - Bill Thoen

You have to hit the disk twice if you use an index -- once to read the
index and once to read the table itself. If the planner guesses that
an operation involves most of the records in a table, it will avoid
indexes and just seqscan, because it's faster.

- Josh / eggyknap