Frankly speaking I would refrain from adding `table WITH ...` It is not clear if you allow to add hints at subquery level. I think we should allow that. For instance, https://github.com/ossc-db/pg_hint_plan allows top-level hints only, and that is really hard to use when SQL is build dynamically.
In my experience (it comes mostly from OracleDB), the most important/often used hints are as follows: * no_merge select * from (select /*+ no_merge*/ from ...) subquery, emp e where subquery.id=42 The hint makes optimizer treat braces that surround no_merge subquery as strict, so it does not bring new tables inside, and it does not allow for the tables to escape. However optimizer is allowed to push predicates (e.g. subquery.id=42 can be pushed to subquery) ^^^ this is very important hint. And in PostgreSQL world they often use OFFSET 0 or something like that. * leading(table_name_a, table_name_b, table_name_c) to specify which tables should be used for staring the query. * index(table_name index_name) * cardinality(table_name 1000) to augment cardinality estimations. This is especially handy for cases like table(...) when optimizer just can't estimate properly. * use_nl / use_hash to specify nested loops vs hash join * resource management hints like parallel(table, 8). Note: the exact hint names can vary, however it would be nice if we could capture and express the above intentions. I'm not sure if there are legal issues with use of Oracle-named hints, however they seem to be quite popular in the wild. PS I'm really glad the topic is pushed forward, however I can't contribute much at this point :-/ Vladimir
