Error reporting and non-existent indexes

Gurmokh commented 1 year ago

Installing this version https://github.com/ossc-db/pg_hint_plan/releases/download/REL14_1_4_0/pg_hint_plan14-1.4-1.el8.x86_64.rpm

Testing to see how queries behave if there are errors in the hint. If there is no matching index in the IndexScan hint the planner estimates the cost to a really large number pushing the planner into JIT functions. See below where I have misspelled an index name. Not always likely but if a developer gets this wrong it could be quite damaging. Would there not be a way to ignore the hint if there is no matching index ?

postgres=# /*+ IndexScan(atable idx_datable_a) */ explain (analyse) select a, b from atable where a>200;
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Seq Scan on atable  (cost=10000000000.00..10000000170.00 rows=9800 width=8) (actual time=17.398..18.118 rows=9800 loops=1)
   Filter: (a > 200)
   Rows Removed by Filter: 200
 Planning Time: 0.152 ms
 JIT:
   Functions: 2
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 0.378 ms, Inlining 2.494 ms, Optimization 10.649 ms, Emission 4.166 ms, Total 17.687 ms
 Execution Time: 18.820 ms
(9 rows)

Also the logger shows the index as being used even though it does not exist.

2023-02-16 12:21:16.671 UTC [55] LOG:  pg_hint_plan:
    used hint:
    IndexScan(atable idx_datable_a)
    not used hint:
    duplication hint:
    error hint:

samimseih commented 1 year ago

I believe this recent commit addresses the issue, and this can be marked as resolved.

https://github.com/ossc-db/pg_hint_plan/commit/a3646e1b073cb51c6a9aa0b5af898e4342c330d5

michaelpq commented 1 year ago

If you still think that we could do better, feel free to reopen this issue.

ossc-db / pg_hint_plan

Error reporting and non-existent indexes #126