Re: Inserts or Updates - Mailing list pgsql-performance

> -----Original Message-----
> From: Andy Colson [mailto:andy@squeakycode.net]
> Sent: Tuesday, February 07, 2012 4:47 PM
> To: Ofer Israeli
> Cc: pgsql-performance@postgresql.org; Olga Vingurt; Netta Kabala
> Subject: Re: [PERFORM] Inserts or Updates
>
> On 2/7/2012 4:18 AM, Ofer Israeli wrote:
>> Hi all,
>>
>> We are currently "stuck" with a performance bottleneck in our server
>> using PG and we are thinking of two potential solutions which I would be
>> happy to hear your opinion about.
>>
>> Our system has a couple of tables that hold client generated
>> information. The clients communicate *every* minute with the server and
>> thus we perform an update on these two tables every minute. We are
>> talking about ~50K clients (and therefore records).
>>
>> These constant updates have made the table sizes to grow drastically and
>> index bloating. So the two solutions that we are talking about are:
>>
>
> You dont give any table details, so I'll have to guess.  Maybe you have
> too many indexes on your table?  Or, you dont have a good primary index,
> which means your updates are changing the primary key?
>
> If you only have a primary index, and you are not changing it, Pg should
> be able to do HOT updates.
>
> If you have lots of indexes, you should review them, you probably don't
> need half of them.
>
>
> And like Kevin said, try the simple one first.  Wont hurt anything, and
> if it works, great!
>
> -Andy
>


On 2/7/2012 11:40 AM, Ofer Israeli wrote:
 > Hi Andy,
 >
 > The two tables I am referring to have the following specs:
 > Table 1:
 > 46 columns
 > 23 indexes on fields of the following types:
 > INTEGER - 7
 > TIMESTAMP - 2
 > VARCHAR - 12
 > UUID - 2
 >
 > 23 columns
 > 12 indexes on fields of the following types:
 > INTEGER - 3
 > TIMESTAMP - 1
 > VARCHAR - 6
 > UUID - 2
 >
 > All indexes are default indexes.
 >
 > The primary index is INTERGER and is not updated.
 >
 > The indexes are used for sorting and filtering purposes in our UI.
 >
 >
 > I will be happy to hear your thoughts on this.
 >
 > Thanks,
 > Ofer
 >

Fixed that top post for ya.

Wow, so out of 46 columns, half of them have indexes?  That's a lot.
I'd bet you could drop a bunch of them.  You should review them and see
if they are actually helping you.  You already found out that maintain
all those indexes is painful.  If they are not speeding up your SELECT's
by a huge amount, you should drop them.

Sounds like you went thru your sql statements and any field that was
either in the where or order by clause you added an index for?

You need to find the columns that are the most selective.  An index
should be useful at cutting the number of rows down.  Once you have it
cut down, an index on another field wont really help that much.  And
after a result set has been collected, an index may or may not help for
sorting.

Running some queries with EXPLAIN ANALYZE would be helpful.  Give it a
run, drop an index, try it again to see if its about the same, or if
that index made a difference.

-Andy