They added plan hinting, but sadly not via comments. To use hinting an app needs to issue a `SET` command, which adds round trip latency for each query. Need to use quite unergonomic query batching/pipelining to negate that latency.
> To use hinting an app needs to issue a `SET` command, which adds round trip latency for each query
You can send the SET and the actual SQL command as a single String (delimited by ; obviously), so there is no additional round trip required.
Additionally: there is the possibility to save such a plan advice to be applied without issuing a "SET pg_plan_advice.advice" before running the statement. So this can also be applied to queries that can't be changed in the source code. This is provided by the extension pg_stash_advice
> They added plan hinting, but sadly not via comments.
Comments are comments, not syntax. Plan hints have syntax. They are different concepts, and mixing syntax inside comments is a great way to break things.
Huh, I thought most SET commands were handled entirely client-side? Eg setting character encoding doesn’t require a round trip.
plan hinting is a crutch. a useful one to have, and lifesaver when you need it, but a crutch nonetheless.
what i wish postgres would do is to replan when it wildly mis-estimates row counts in deeply nested queries. just because you decided on a linear scan join when you thought that sub-query had 4 rows, doesn’t mean you need to commit to it when that sub-query turns out to actually have 4 million rows.
if you need query plan hints, it’s most likely because some aspect of postgres’ statistics about your tables is very out of line. and, given the very nature of stats collection, i.e., it being a lossy process, there are many distributions of data that it simply cannot detect & accurately account for.