BitmapScan not applied when using a table alias

[MatteoSp]

Using 'pg_hint_plan' v 1.4 on Postgres 14.7

I was fighting to have may hint used in quite simple query:

/*+ BitmapScan("Skus") */
SELECT TS.*
  FROM public."Skus" AS TS
 WHERE TS."Barcodes" && '{ "THE_BARCODE" }'
 LIMIT 10;

for which the logs say:

NOTICE:  pg_hint_plan[qno=0x58]: setup_hint_enforcement no hint applied: relation=35103(Skus), inhparent=0, current_hint=0x55f82d7b9080, hint_inhibit_level=0, scanmask=0x1f
NOTICE:  pg_hint_plan[qno=0x58]: HintStateDump: {used hints:(none)}, {not used hints:BitmapScan(Skus)}, {duplicate hints:(none)}, {error hints:(none)}

It was very frustrating not being able to understand why my hint was not used, then - just for trying - I executed:

/*+ BitmapScan("Skus") */
SELECT "Skus".* 
  FROM public."Skus"
 WHERE "Barcodes" && '{ "THE_BARCODE" }'
 LIMIT 10;

and my hint got used, with logs saying:

NOTICE:  pg_hint_plan[qno=0x5a]: setup_hint_enforcement index deletion: relation=35103(Skus), inhparent=0, current_hint_state=0x55f82d7b9038, hint_inhibit_level=0, scanmask=0x4
NOTICE:  pg_hint_plan[qno=0x5a]: HintStateDump: {used hints:BitmapScan(Skus)}, {not used hints:(none)}, {duplicate hints:(none)}, {error hints:(none)}

I also tried to put the alias in the hint comment: `/+ BitmapScan("TS") /', but it has no effect.

Am I missing something or doing any mistake?

[jeffjanes]

The alias "TS" in your query is not enclosed in double quotes, so it gets case folded down to "ts", just like table names do. But in the hint, case folding does not apply, so "TS" is not equal to ts. Use the lower case spelling of the hint /*+ BitmapScan(ts) */, and it works.

[MatteoSp]

thanks. and there's no way to use the table names in hints when the query uses aliases?

[michaelpq]

The handling of the object names is documented. See https://pg-hint-plan.readthedocs.io/en/latest/hint_details.html#upper-and-lower-case-handling-in-object-names.

[MatteoSp]

thanks @michaelpq. I'm not sure how to interpret this:

Unlike the way PostgreSQL handles object names, pg_hint_plan compares bare object names in hints against the database internal object names in a case-sensitive manner. Therefore, an object name TBL in a hint matches only “TBL” in the database and does not match any unquoted names like TBL, tbl or Tbl.

From my understanding the "Skus" in the hint should match the table name, is that correct? Is - as suggested by @jeffjanes - putting the alias within the hint the only way to get it work given I have alias in the query?