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

Re: Insertions slower than Updates?

From: Steve Horn <steve(at)stevehorn(dot)cc>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: Ofer Israeli <oferi(at)checkpoint(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>, Netta Kabala <nettak(at)checkpoint(dot)com>, Olga Vingurt <olgavi(at)checkpoint(dot)com>
Subject: Re: Insertions slower than Updates?
Date: 2012-02-20 19:32:35
Message-ID: CAFLkBaXwDE6bAvo7rdL04rZdHSy-kkmaZrGSPwGBHBc2_zaUNQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
If the updates don't hit indexed columns (so the indexes don't need to be
rebuilt), then the update would be very fast.

Inserts would always affect the index causing it to constantly need
modifying.

If you're doing a lot of INSERTs in a batch operation, you may want to
consider dropping the indexes and recreating at the end.

On Mon, Feb 20, 2012 at 2:29 PM, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov
> wrote:

> Ofer Israeli <oferi(at)checkpoint(dot)com> wrote:
> > Kevin Grittner wrote:
> >> Ofer Israeli <oferi(at)checkpoint(dot)com> wrote:
> >>> Anyone have any ideas on why the empty db is giving worse
> >>> results??
> >>
> >> Besides the HOT updates being fast, there is the issue of having
> >> space already allocated and ready for the database to use, rather
> >> than needing to make calls to the OS to create and extend files
> >> as space is needed.
> >
> > I thought about this direction as well, but on UPDATES, some of
> > them will need to ask the OS for more space anyhow at least at the
> > beginning of the run, additional pages will be needed.  Do you
> > expect that the OS level allocations are so expensive as to show
> > an ~%40 increase of processing time in average?
>
> Gut feel, 40% does seem high for just that; but HOT updates could
> easily account for that, especially since you said that the tables
> are "heavily indexed".  That is, as long as there are enough updates
> which don't modify indexed columns.
>
> -Kevin
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>



-- 
Steve Horn
http://www.stevehorn.cc
steve(at)stevehorn(dot)cc
http://twitter.com/stevehorn
740-503-2300

In response to

pgsql-performance by date

Next:From: Ofer IsraeliDate: 2012-02-20 20:16:16
Subject: Re: Insertions slower than Updates?
Previous:From: Kevin GrittnerDate: 2012-02-20 19:29:43
Subject: Re: Insertions slower than Updates?

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