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/0x1a0
0x80d030d : index_fetch_heap+0x11d/0x140
0x80d035e : index_getnext+0x2e/0x40
0x81eec9b : IndexNext+0x3b/0x100
0x81e4ddf : ExecScan+0x15f/0x290
0x81eed8d : ExecIndexScan+0x2d/0x50
0x81ddb20 : ExecProcNode+0x1f0/0x2a0
0x81dac6c : standard_ExecutorRun+0xfc/0x160
0x82d0503 : PortalRunSelect+0x183/0x200
0x82d17da : PortalRun+0x26a/0x3c0
0x82cf452 : PostgresMain+0x2282/0x2fc0
0x8097f52 : ServerLoop+0xb1b/0xec2
0x82793d7 : PostmasterMain+0x1237/0x13c0
0x8098b6c : main+0x48c/0x4d4
0xb754fa83 : __libc_start_main+0xf3/0x210
0x8098bd5 : _start+0x21/0x2c

Regards,
Jinhua Luo