[YSQL] Cost based optimization

[ndeodhar]

Jira Link: DB-1656 There are several cost based optimizations needed for YSQL. Some examples, If table and index have the same PK, use table instead of index since table scan is more performant for YB. Today, we choose the index.

EXPLAIN SELECT * FROM airports WHERE iso_region = 'US-CA' LIMIT 1;
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.50 rows=1 width=356)
   ->  Index Scan using airports_idx2 on airports  (cost=0.00..5.04 rows=10 width=356)
         Index Cond: (iso_region = 'US-CA'::text)
(3 rows)

Here airports and airports_idx2 have the same PK and our quiery plan selects index scan.

Another example: https://github.com/yugabyte/yugabyte-db/issues/2959

[ddorian]

@ndeodhar If partial-index has all the columns that the query needs (where + include), always use that ?