Hello.
> 1. Charts are measured in percents of pgbench TPS, right?
Yes, correct. Actual values are calculated as TPS_of_patched / TPS_of_vanilla. TPS was measured using single postgres process (one core) (I was also did tests with multiple processes, but they shows pretty same results).
> 2. For example, is 97% actually 3% degrade?
Yes, such degrade happens for indexes with high correlation and predicates with low selectivity. In such cases 2-4% overhead is caused by index data read and page visibility check. But it is possible to detect such cases in planner and use regular IndexScan instead.
> 3. The results are obtained on actual "sort of TPC-B" script?
You could check testing script (
https://gist.github.com/michail-nikolaev/23e1520a1db1a09ff2b48d78f0cde91d) for SQL queries.
But briefly:
* Vanilla pg_bench initialization
* ALTER TABLE pgbench_accounts drop constraint pgbench_accounts_pkey; -- drop non-required constraint
* UPDATE pgbench_accounts SET bid = TRUNC(RANDOM() * {ROWS_N} + 1 -- randomize BID (used for selectivy predicate)
* UPDATE pgbench_accounts SET aid = TRUNC(RANDOM() * {ROWS_N} + 1) WHERE random() <= (1.0 - {CORRELATION}) -- emulate index correlation by changing some part of AID values
* CREATE index test_index ON pgbench_accounts USING btree(aid, bid) -- create index used for test
* VACUUM FULL;
* VACUUM ANALYZE pgbench_accounts;
* SELECT * FROM pgbench_accounts WHERE aid > {RANDOM} and bid % 100 <= {SELECTIVITY} order by aid limit 50
Thanks,
Michail.
