search

ossc-db / pg_hint_plan

Extension adding support for optimizer hints in PostgreSQL
Other
696 stars 103 forks source link

Documentation questions #113

Closed SteveDirschelTR closed 1 year ago

SteveDirschelTR commented 1 year ago

Reading through the 1.5 doc and I have some clarification questions.

Under "Hint for joining order"

postgres=# /+ postgres# NestLoop(t1 t2) postgres# MergeJoin(t1 t2 t3) postgres# Leading(t1 t2 t3) postgres# /

Is the example above a good example? I realize this section is giving an example of using Leading to force a join order but isn't NestLoop and MergeJoin both join methods? How does one interpret those 2 hints? One is saying to nested loop between t1 and t2 but the other is saying to merge join between t1 and t2. This does not make sense to me... Later in the documentation I see it says with conflicting hints the last one is used so in this case I assume the MergeJoin would be used- is that correct?

Under section 'Using with PL/pgSQL' it shows the hint needs to be after the SELECT because preceding comments are not sent as part of the query. Can hints be placed after the SELECT for non-PL/pgSQL queries rather than being placed before the SELECT or can that only be done for queries in PL/pgSQL?

Under "Distinction between multiple occurances of a table"

"pg_hint_plan identifies the target object by using aliases if exists."

It is not clear to me if a table has an alias does the alias HAVE TO BE used in the hint or can you use the alias OR table name? The example here shows a single table being referenced 2 times and explains you have to use an alias in that case but assume a query with a from clause of FROM table1 t1, table2 t2. In that case based on the info above using the alias t1 and t2 in the hint will work. But could you also use table1 and table2 in the hint or if a table has an alias do you HAVE TO use the alias? If the alias is in upper case does the alias hint need to be in upper case?

Thanks

xzilla commented 1 year ago

A. I believe your interpretation is correct, and I agree that this is not a great example due to the confusion around it. I think it would be clearer to drop the NestLoop hint, though there may be some subtlety we are missing. I'll probably propose a separate patch for that change and see if folks agree.

B. Yes, you can place the query hint after the SELECT in non-pl/pgsql queries, though a preceding hint block is recommended as it is likely to be the most legible.

C. Yes, if the object has an alias name, the hint must refer to the alias. If the alias is unquoted upper case, it will case fold to lower case, and therefore the the hint should use lower case. If the alias is quoted, it will not case fold, and so the hint should use upper case in that scenario.

michaelpq commented 1 year ago

Any patches to improve the docs are welcome. Please note that I have largely reworked the documentation as of commit e28b9d461172a90c60f8830f7e3f0446724dd9b5, still your remarks about improvements apply. Seeing no patches proposed, I am just closing that for now.