3 ms·

It doesn’t exist indeed. I understand the PG maintainers stance, but I can’t agree with it. First because “we get bugs fixed quickly” is true but not true enou

It doesn’t exist indeed. I understand the PG maintainers stance, but I can’t agree with it. First because “we get bugs fixed quickly” is true but not true enough (my own experience: https://www.postgresql.org/message-id/17540-7aa1855ad5ec18b4%40postgresql.org https://www.postgresql.org/message-id/17540-7aa1855ad5ec18b4... has not been fixed since reported 1.5y ago) Second because all the knobs available to tune the query planner (listed in your last link) are obscure and require deep understanding of Postgres internals to make sense of them, which means that only a fraction of people understand them whereas query plans are more pedestrian. They’re also often database-scoped or session-scoped rather than query-scoped, it’s a huge risk of making things worse rather than better.

phamilton 3y ago
All the knobs for query planning and cost estimation can be scoped to the transaction with `set local <key>=<value>`.
franckpachot 3y ago
Even if bugs are fixed instantly, nobody will apply a patch in production withiut previous testing. Changing system-wide behavior to fix a single query may make things worse. Hints are the only way to fix at the scope of one statement with the guarantee that it doesn't break others
RaftPeople 3y ago
> I understand the PG maintainers stance, but I can’t agree with it I don't just not agree, I truly don't understand how they can take that position knowing the nature of the problem. "The problem"=optimizing over a large space in limited time without complete info. It will never be perfect, there will always be exceptions.
nextaccountic 3y ago
Okay so it isn't entirely clear to me, can the pg_hint_plan extension (linked in the OP) do the simple thing where we specify, for each table, which index to use? I can't find it here https://github.com/ossc-db/pg_hint_plan/blob/master/docs/hint_table.md https://github.com/ossc-db/pg_hint_plan/blob/master/docs/hin... Because, the mssql WITH(INDEX()) is simple and intuitive. This hint table stuff seems complicated, and it's unclear to me if they can do the simple thing (Note: WITH(INDEX()) is different than specifying whether you will do SeqScan or IndexScan. it's, like, in SQL we can give a name to an index or to a constraint, and one may want to be able to do IndexScan, yes, but with a specific named index)

phamilton 3y ago

All the knobs for query planning and cost estimation can be scoped to the transaction with `set local <key>=<value>`.

franckpachot 3y ago

Even if bugs are fixed instantly, nobody will apply a patch in production withiut previous testing. Changing system-wide behavior to fix a single query may make things worse. Hints are the only way to fix at the scope of one statement with the guarantee that it doesn't break others

RaftPeople 3y ago

> I understand the PG maintainers stance, but I can’t agree with it I don't just not agree, I truly don't understand how they can take that position knowing the nature of the problem. "The problem"=optimizing over a large space in limited time without complete info. It will never be perfect, there will always be exceptions.

nextaccountic 3y ago

Okay so it isn't entirely clear to me, can the pg_hint_plan extension (linked in the OP) do the simple thing where we specify, for each table, which index to use? I can't find it here https://github.com/ossc-db/pg_hint_plan/blob/master/docs/hint_table.md https://github.com/ossc-db/pg_hint_plan/blob/master/docs/hin... Because, the mssql WITH(INDEX()) is simple and intuitive. This hint table stuff seems complicated, and it's unclear to me if they can do the simple thing (Note: WITH(INDEX()) is different than specifying whether you will do SeqScan or IndexScan. it's, like, in SQL we can give a name to an index or to a constraint, and one may want to be able to do IndexScan, yes, but with a specific named index)