[YSQL] for non-prepared statements, optimize pg_statistic system table lookups

[kmuthukk]

When not using a prepared statement that's repeatedly used for re-execution, if the query planner doesn't have an obvious plan based on indexes or primary key for a given statement, it ends up going to the pg_statistic table.

For example, if you have two tables t1 and t2 both with two columns k (primary key) and v, then:

this join:

SELECT * from t1, t2 WHERE t1.k = 'k-123'

doesn't seem to need the help of pg_statistic table, whereas this one does:

SELECT * from t1, t2 WHERE t1.k = t2.k

And for such queries, if a "prepared" statement is not being used, then every execute ends up doing extra lookups on the pg_statistic table.

For the above example, we see two calls to pg_statistic each time the statement is prepared (one for t1 and one for t2). Given that this Postgres system table lives in yb-master, this incurs additional RPCs calls from yb-tserver to yb-master leaders and also increases the likelihood of yb-master becoming a bottleneck.

Test program which illustrates the issue:
https://gist.github.com/kmuthukk/611813ab4685179038654486d2ae1c0a

The workaround would be to use a prepared statement; but since we do find a lot of use of direct "execute" without a separate prepare step in the real-world, we should optimize/cache these lookups on each server rather than go to yb-master each time.

For python specifically, the psycopg2 driver doesn't have a cursor.prepare() API. But to work around you can use Postgres' explicity PREPARE statement.

For example, changing:

  for idx in range(num_rows):
     cur.execute("""SELECT * from t1, t2 WHERE t1.k = t2.k AND %s = %s LIMIT 1""", ("foo", "foo"))

to:

  cur.execute("PREPARE myplan as SELECT * from t1, t2 WHERE t1.k = t2.k AND $1 = $2 LIMIT 1")
  for idx in range(num_rows):
    cur.execute("""EXECUTE myplan(%s, %s)""", ("foo", "foo"))

addresses the issue.

[m-iancu]

Fixed by c41c3c9.