make hint relname comparison case insensitive

[samimseih]

An issue I recently encountered on the field is the hints being case sensitive. i.e. for table a, supplying the table name as A caused the hint to not be used.

postgres=# 
DROP TABLE a;
CREATE TABLE a (a1 TIMESTAMP, a2 TIMESTAMP, a3 TEXT);
CREATE INDEX a_ix2 ON a(a2, a1, a3);
CREATE INDEX a_ix3 ON a(a2, a3);

DROP TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX
postgres=#  /*+ IndexScan(A a_ix2) */ explain (buffers, analyze) select * from a WHERE a1 <= '2024-02-06' AND a2 IS NULL AND a3 = '0';
                                                QUERY PLAN                                                
----------------------------------------------------------------------------------------------------------
 Index Scan using a_ix3 on a  (cost=0.15..8.17 rows=1 width=48) (actual time=0.003..0.004 rows=0 loops=1)
   Index Cond: ((a2 IS NULL) AND (a3 = '0'::text))
   Filter: (a1 <= '2024-02-06 00:00:00'::timestamp without time zone)
   Buffers: shared hit=2
 Planning:
   Buffers: shared hit=38 read=2
 Planning Time: 0.375 ms
 Execution Time: 0.016 ms
(8 rows)

postgres=#  /*+ IndexScan(a a_ix2) */ explain (buffers, analyze) select * from a WHERE a1 <= '2024-02-06' AND a2 IS NULL AND a3 = '0';
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Index Scan using a_ix2 on a  (cost=0.15..8.19 rows=1 width=48) (actual time=0.011..0.012 rows=0 loops=1)
   Index Cond: ((a2 IS NULL) AND (a1 <= '2024-02-06 00:00:00'::timestamp without time zone) AND (a3 = '0'::text))
   Buffers: shared hit=2
 Planning:
   Buffers: shared hit=5
 Planning Time: 0.308 ms
 Execution Time: 0.041 ms
(7 rows)

This is documented [1] and the fix is to use a lower case table name in the hint, I am not sure this should be necessary. I wonder if we should just really be doing a case insensitive string compare as below?

@@ -1445,7 +1445,7 @@ RelnameCmp(const void *a, const void *b)
        const char *relnamea = *((const char **) a);
        const char *relnameb = *((const char **) b);

-       return strcmp(relnamea, relnameb);
+       return strcasecmp(relnamea, relnameb);
 }

Thoughts?

[1] https://github.com/ossc-db/pg_hint_plan/blob/master/docs/hint_details.md#upper-and-lower-case-handling-in-object-names

[samimseih]

I quickly realized this will not play well with double quoted relation names. My apologies.