Re: [HACKERS] insert/update performance

2016-01-27 Thread Jinhua Luo
> > But what kind of rows would satisfy heap_page_prune() and what would not? > > In my case all updates are doing the same thing (there is no HOT > updates, obviously), but why some updated rows are reported by > heap_page_prune() but the others are not? And it's also a random > issue. That means

Re: [HACKERS] insert/update performance

2016-01-26 Thread Jinhua Luo
Ok, I found the vaccum output is correct. I check the codes of lazy_scan_heap(), the rows to be removed are reported in two parts, one is return of heap_page_prune(), the other is ItemIdIsDead() when scanning the page. After scanning all pages of the relation, those rows would be clean up in:

Re: [HACKERS] insert/update performance

2016-01-23 Thread Amit Kapila
On Sat, Jan 23, 2016 at 1:16 PM, Jinhua Luo wrote: > > Hi, > > The vacuum doesn't recycle the rows obsoleted by update? > It does free up the space which can be used by future inserts. > I don't think > so. In the above vacuum result, I do not delete any rows, but the >

Re: [HACKERS] insert/update performance

2016-01-23 Thread Jinhua Luo
Hi, 2016-01-23 18:40 GMT+08:00 Amit Kapila : > At the moment, I am also not able to see why it is so. You might > want to first try with a simple test (Can you extract insert/update > statements from application and run it manually for couple of times > and then run

Re: [HACKERS] insert/update performance

2016-01-23 Thread Tom Lane
Jinhua Luo writes: > I have a table with 70 columns, and 6 indexes. The data flow is a > special OLTP model: frequent inserts (2000 tps), and each inserted row > would be updated very soon (i.e. the number of inserts is equal to the > number of updates). Do those predictable

Re: [HACKERS] insert/update performance

2016-01-23 Thread Jinhua Luo
And I also found the pg_stat_all_tables may be not so accurate. testdb=# truncate test; testdb=# select pg_stat_reset_single_table_counters(42515); testdb=# select n_tup_ins,n_tup_upd,n_tup_hot_upd,n_tup_del,n_live_tup,n_dead_tup from pg_stat_all_tables where relid=42515; n_tup_ins | n_tup_upd |

Re: [HACKERS] insert/update performance

2016-01-23 Thread Jinhua Luo
2016-01-23 23:00 GMT+08:00 Tom Lane : > Jinhua Luo writes: >> I have a table with 70 columns, and 6 indexes. The data flow is a >> special OLTP model: frequent inserts (2000 tps), and each inserted row >> would be updated very soon (i.e. the number of

[HACKERS] insert/update performance

2016-01-22 Thread Jinhua Luo
Hi All, Here is my test environment: postgresql 9.4.5, Ubuntu 14.04, 8 CPU core, 8GB ram, SCSI hard disk I have a table with 70 columns, and 6 indexes. The data flow is a special OLTP model: frequent inserts (2000 tps), and each inserted row would be updated very soon (i.e. the number of

Re: [HACKERS] insert/update performance

2016-01-22 Thread Amit Kapila
On Sat, Jan 23, 2016 at 12:13 PM, Jinhua Luo wrote: > > Hi All, > > Here is my test environment: > > postgresql 9.4.5, Ubuntu 14.04, 8 CPU core, 8GB ram, SCSI hard disk > > I have a table with 70 columns, and 6 indexes. The data flow is a > special OLTP model: frequent

Re: [HACKERS] insert/update performance

2016-01-22 Thread Jinhua Luo
Hi, The vacuum doesn't recycle the rows obsoleted by update? I don't think so. In the above vacuum result, I do not delete any rows, but the vacuum still recycles a fraction of rows, obviously they're obsoleted by update. I know plain vacuum (without full option) do not reduce the size of the