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?

You're assuming that seq scan is making it slow. You can always use
the enable_xxx settings to turn off sequential scan etc to see if it
runs faster with an index. Also, you might have a tuning issue going
on and indexed lookups would be faster.

If you're hitting every record, it's probably best to do a seq scan as
index scans, as previously mentioned hit both the index and the table.

What's your work_mem set to? What about random_page_cost,
effective_cache_size, and shared_buffers?