Re: insert/update performance

>
> 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 sometimes heap_page_prune() would report all
> removable rows, and sometimes it reports no rows.
>

I check the codes again.

The heap_page_prune() would skip items if ItemIdIsDead() returns true.

That means some obsoleted items are flagged dead before vacuum, and I
found 3 places:

1) heap_page_prune_opt() --> heap_page_prune() --> ItemIdSetDead()
2) _bt_check_unique() --> ItemIdMarkDead()
3) _bt_killitems() --> ItemIdMarkDead()

In my case, the first one happens most frequently.
And it's interesting that it's invoked from select statement!
0x80ca000 : heap_page_prune_opt+0x0/0x1a00x80d030d : index_fetch_heap+0x11d/0x1400x80d035e :
index_getnext+0x2e/0x400x81eec9b: IndexNext+0x3b/0x1000x81e4ddf : ExecScan+0x15f/0x2900x81eed8d :
ExecIndexScan+0x2d/0x500x81ddb20: ExecProcNode+0x1f0/0x2a00x81dac6c : standard_ExecutorRun+0xfc/0x1600x82d0503 :
PortalRunSelect+0x183/0x2000x82d17da: PortalRun+0x26a/0x3c00x82cf452 : PostgresMain+0x2282/0x2fc00x8097f52 :
ServerLoop+0xb1b/0xec20x82793d7: PostmasterMain+0x1237/0x13c00x8098b6c : main+0x48c/0x4d40xb754fa83 :
__libc_start_main+0xf3/0x2100x8098bd5: _start+0x21/0x2c
 


Regards,
Jinhua Luo