[YSQL] Catalog cache misses for <> operator on pkey/indexed columns

[m-iancu]

Jira Link: DB-15658

Description

lsm does not support pushing down the <> (or !=) operator but the planner will try to check if it exists in pg_amop. Also looks like we do not enable negative caching (caching for not-found lookups) for pg_amop so this will be checked each time the planner tries to plan this query.
Mitigations could be to use prepared statements to avoid re-planning each time or rewriting the query to avoid that operator (e.g. k > ? OR k < ?).

Minimal repro:

1. Setup

CREATE TABLE test(k text primary key);
SELECT * FROM test WHERE k <> 'a';  
-- ^this should normally load whatever catalog entries this query needs in the cache

SET yb_debug_log_catcache_events = TRUE;
SET client_min_messages=LOG;

2. Now run the query repeatedly -- the cache miss will happen each time.

SELECT * FROM test WHERE k <> 'a';
SELECT * FROM test WHERE k <> 'a';
SELECT * FROM test WHERE k <> 'a';
SELECT * FROM test WHERE k <> 'a';

Local output:

yugabyte=# SELECT * FROM test WHERE k <> 'a';
LOG:  Catalog cache miss on cache with id 3:
Target rel: pg_amop (oid : 2602), index oid 2654
Search keys: 531, s, 9922
 k
---
(0 rows)

yugabyte=# SELECT * FROM test WHERE k <> 'a';
LOG:  Catalog cache miss on cache with id 3:
Target rel: pg_amop (oid : 2602), index oid 2654
Search keys: 531, s, 9922
 k
---
(0 rows)

yugabyte=# SELECT * FROM test WHERE k <> 'a';
LOG:  Catalog cache miss on cache with id 3:
Target rel: pg_amop (oid : 2602), index oid 2654
Search keys: 531, s, 9922
 k
---
(0 rows)

yugabyte=# SELECT * FROM test WHERE k <> 'a';
LOG:  Catalog cache miss on cache with id 3:
Target rel: pg_amop (oid : 2602), index oid 2654
Search keys: 531, s, 9922
 k
---
(0 rows)

Issue Type

kind/bug

Warning: Please confirm that this issue does not contain any sensitive information

• I confirm this issue does not contain any sensitive information.