PostgreSQL记录和卸载表性能与锁表使用情况的比较

我在试着测量 UPDATE 打开速度 LOGGED 和 UNLOGGED PostgreSQL中的表(10.3)。我想用 LOCK TABLE 以防止其他应用程序相互干扰。

如果 更新 执行时不使用 锁定表 ,我得到~1K 记录的 桌子,~4K 已卸载 表。

如果 更新 使用 锁定表 ,两种表类型的结果相同。

为什么? 锁定表 在两种表类型中返回相同的结果?

我的pl/pgsql函数:

CREATE OR REPLACE FUNCTION public.myfunction(user_id integer, unitprice numeric(10,6), 
            islock boolean, useunlogged boolean) RETURNS integer AS
$BODY$
declare howMuch integer;
begin       
    if islock then
        if useunlogged then 
            LOCK TABLE credittable_unlogged IN ACCESS EXCLUSIVE MODE;
        else
            LOCK TABLE credittable IN ACCESS EXCLUSIVE MODE;
        end if;
    end if;
    if useunlogged then     
        select (credit_amount/unitprice)::integer into howMuch from credittable where userid=user_id and credit_amount>=unitprice;
        if howMuch is null then 
            select 0 into howMuch;
        else
            update credittable set credit_amount=credit_amount-unitprice where userid=user_id;
        end if;    
    else 
        select (credit_amount/unitprice)::integer into howMuch from credittable_unlogged where userid=user_id and credit_amount>=unitprice;
        if howMuch is null then 
           select 0 into howMuch;
        else
            update credittable_unlogged set credit_amount=credit_amount-unitprice where userid=user_id;
        end if;  
    end if;
    RETURN howMuch;
 end;
 $BODY$
   LANGUAGE plpgsql VOLATILE
    COST 100;
 ALTER FUNCTION public.myfunction(integer, numeric, boolean, boolean)
    OWNER TO postgres;

我的Java代码:

for(int i=1;i<=4;i++){
    long startTime = System.nanoTime();
    int counter = 0;        
    while ((System.nanoTime() - startTime) < 1000000000L) {
        CallableStatement callst = null;
        try {
            String sql = "{? = call public.myfunction(?,?,?,?) }";
            callst = con.prepareCall(sql);
            callst.registerOutParameter(1, Types.INTEGER);
            callst.setInt(2, 123456);
            callst.setBoolean(3, (i > 2));
            callst.setBoolean(4, (i%2 != 0));
            callst.setBigDecimal(3, BigDecimal.valueOf(0.001));
            callst.execute();
            int howMuch = callst.getInt(1);                
            counter++;                
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (callst != null) {
                callst.close();
            }
        }
    }
    System.out.println("Counter :"+counter);
}