YSQL performance

[rkarthik007]

Jira Link: DB-2060
This is a master issue relating to performance of YSQL, as well as benchmarking it using standard tools. The goals of this effort are to:

• Improve performance of YSQL
• Make it easy to run various standard benchmarks with ease
• Ability to test performance across a variety of environments (public clouds, Kubernetes, etc) and come up with some reference numbers.

The following benchmarks are going to be supported at the end of this effort: TPCC, YCSB for both YSQL and YCQL, sysbench and pg_bench / ysql_bench (note that ysql_bench is the same as pg_bench but with modifications to run optimally against YugabyteDB).

Benchmarks

YCSB
✅ [YCSB] Create a YSQL benchmark (Update: docs here)
✅ [YCSB] Create a YCQL benchmark (Update: docs here)
✅ [YCSB] Add a downloadable package and instructions for YugabyteDB
⬜️ [YCSB] Document expected results of benchmark per release

TPCC
✅ [TPCC] Create a Java-based YSQL benchmark from oltpbench
✅ [TPCC] Create a YSQL benchmark workload (Update: repo)
✅ [TPCC] Instructions to download and run TPCC (Update: docs here)
⬜️ [TPCC] Verify implementation of oltpbench against the official TPCC spec
⬜️ [TPCC] Document expected results of benchmark per release

PG_BENCH / YSQL_BENCH
✅ [YSQL_BENCH] modify init phase to use multiple smaller transactions #3229
⬜️ [YSQL_BENCH] Add docs instructions to run (Update: instructions here)
⬜️ [YSQL_BENCH] Document expected results of benchmark per release

SYSBENCH
⬜️ [SYSBENCH] Add docs instructions to run
⬜️ [SYSBENCH] Document expected results of benchmark per release

OLTP Query performance

The following tasks are related mostly to improving the query performance (both INSERT and SELECT) during the runtime of the app.
✅ Batch writes to speed up COPY and multi-row inserts #2934
✅ Skip PostgreSQL ordering if the query uses PK ordering #2792
✅ Cache foreign key checks #3328
✅ Improve index scan to batch select rows from base table #3103
✅ Push down updates that involve arithmetic operators #2969
✅ Improvements to batch selects #2960
⬜️ Push down expressions and part of query plan #2040
⬜️ Single round-trip distributed transactions #3220
⬜️ Cost based optimizations for YSQL #3101

Data loading

The following set of tasks are to improve the loading of data through YSQL.
✅ Batch writes to speed up COPY and multi-row inserts #2934
✅ Provisional writes not cleaned up resulting in slow loading of data #3111
✅ Cache foreign key checks #3328
✅ Reuse iterators during txn conflict resolution #3521
⬜️ Batch FK trigger checks #2951
⬜️ Transactional data load performance improvements #3656