[tibiapejagala]

I had to use the CTE trick few times to solve same nasty query plan in Postgres. While it worked well for me I would still prefer query hints. They are explicit, so the intent is obvious: “Dear future developer/me mind the execution plan”. A CTE might get removed by mistake or not be reevaluated if it’s still needed after Postgres version/statistics/index changes. Also as mentioned if you are still before pg14 there is this issue that you will have to materialize your CTE to keep the hack working.

I remember that the most irritating brownout I got due to a query plan change was when a 50 millisecond query started to use a seq scan, but on a table under 10 million rows, so it wasn’t obviously hanging like in the article, but took like 2-3s. It was just enough to fly under radar for our monitoring, but to make some important process much slower.

[rst]

If you explicitly add 'materialized' to the CTE declaration -- that is:

  with dummy_table_name as materialized
    (select stuff from table ...)

the intent becomes explicit. (FWIW, while the nonstandard "materialized" keyword here doesn't have the grammatical form of hints in other DBs, I've still described it in my org as "the only hint Postgres supports" because the closest equivalent in, say, Oracle, is a hint -- the apparently undocumented /+ materialize /)