Re: [PERFORM] Indexes and Primary Keys on Rapidly Growing Tables

On 2/20/12 2:06 PM, Alessandro Gagliardi wrote: > . But first I just want to know if people > think that this might be a viable solution or if I'm barking up the wrong > tree. Batching is usually helpful for inserts, especially if there's a unique key on a very large table involved. I suggest als

[PERFORM] Indexes and Primary Keys on Rapidly Growing Tables

New question regarding this seen_its table: It gets over 100 inserts per second. Probably many more if you include every time unique_violation occurs. This flood of data is constant. The commits take too long (upwards of 100 ms, ten times slower than it needs to be!) What I'm wondering is if it wou

Re: [PERFORM] Why so slow?

Ah, that did make a big difference! It went from taking 10x as long to taking only 1.5x as long (about what I would have expected, if not better.) Thank you! On Fri, Feb 17, 2012 at 9:29 PM, Ants Aasma wrote: > On Feb 17, 2012 8:35 PM, "Alessandro Gagliardi" > wrote: > > Here is the EXPLAIN: ht

Re: [PERFORM] Insertions slower than Updates?

Ofer Israeli wrote: >Hi all, > In performance testing we're doing we are currently running two scenarios: > 1. Starting from an empty db, therefore all operations are INSERTs. > 2. Starting from an existing db - thereby UPDATing all records. > I should also mention that the tables we are deal

Re: [PERFORM] Insertions slower than Updates?

Kevin Grittner wrote: > Ofer Israeli wrote: >> Kevin Grittner wrote: >>> Ofer Israeli 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

Re: [PERFORM] Insertions slower than Updates?

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 index

Re: [PERFORM] Insertions slower than Updates?

Ofer Israeli wrote: > Kevin Grittner wrote: >> Ofer Israeli 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 needi

Re: [PERFORM] Insertions slower than Updates?

Kevin Grittner wrote: > Ofer Israeli 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 cre

Re: [PERFORM] Insertions slower than Updates?

Ofer Israeli wrote: > INSERT should be cheaper than UPDATE due to only dealing with one > record instead of two. ... unless the UPDATE is a HOT update, in which case the indexes don't need to be touched. > Anyone have any ideas on why the empty db is giving worse > results?? Besides the H

[PERFORM] Insertions slower than Updates?

Hi all, In performance testing we're doing we are currently running two scenarios: 1. Starting from an empty db, therefore all operations are INSERTs. 2. Starting from an existing db - thereby UPDATing all records. I should also mention that the tables we are dealing with are heavily indexed

Re: [PERFORM] Query slow as function

On Sat, Feb 18, 2012 at 8:50 AM, Steve Horn wrote: > Hello all! > > I have a very simple query that I am trying to wrap into a function: > > SELECT gs.geo_shape_id AS gid, > gs.geocode > FROM geo_shapes gs > WHERE gs.geocode = 'xyz' > AND geo_type = 1 > GROUP BY gs.geography, gs.geo_shape_id, gs.g