This thread is a follow-up of thread [1] where I don't have a good writing to describe the issue and solution in my mind. So I start this thread to fix that and also enrich the topic by taking the advices from Ashutosh, Tomas and Tom.
Inaccurate statistics is not avoidable and can cause lots of issue, I don't
think we can fix much of them, but the issue I want to talk about now like
:select * from t where a = x and b = y, and we have index on (a, b) and (a,
c);
The current implementation may choose (a, c) at last while I think we should
always choose index (a, b) over (a, c).
Why will the (a, c) be choose? If planner think a = x has only 1 row, it
will cost
the index (a, b) as "an index access to with 2 qual checking and get 1 row
+ table
scan with the index result,". the cost of (a, c) as "an index access with
1 qual
and get 1 row, and table scan the 1 row and filter the another qual". There
is no cost
difference for the qual filter on index scan and table scan, so the final
cost is
exactly same. If the cost is exactly same, which path is found first,
which one will be choose at last. You can use the attached reproduced.sql
to
reproduce this issue.
The solution in my mind is just hacking the cost model to treat the qual
filter
on table scan is slightly higher so that the index (a, b) will be always
choose.
At the same time, planner still think only 1 row returned which maybe wrong,
but that's the issue I can fix here and will not impact on the final index
choose
anyway.
The one-line fix describe the exact idea in my mind:
+++ b/src/backend/optimizer/path/costsize.c
@@ -730,6 +730,13 @@ cost_index(IndexPath *path, PlannerInfo *root, double
loop_count,
cpu_run_cost += cpu_per_tuple * tuples_fetched;
+ /*
+ * To make the planner more robust to handle some inaccurate
statistics
+ * issue, we will add a extra cost to qpquals so that the less
qpquals
+ * the lower cost it has.
+ */
+ cpu_run_cost += 0.01 * list_length(qpquals);
+
This change do fix the issue above, but will it make some other cases
worse? My
answer is no based on my current knowledge, and this is most important place
I want to get advised. The mainly impact I can figure out is: it not only
change the cost difference between (a, b) and (a, c) it also cause the cost
difference between Index scan on (a, c) and seq scan. However the
cost different between index scan and seq scan are huge by practice so
I don't think this impact is harmful.
I test TPC-H to see if this change causes any unexpected behavior, the
data and index are setup based on [2], the attached normal.log is the plan
without this patch and patched.log is the plan with this patch. In summary,
I
didn't find any unexpected behaviors because of that change. All the plans
whose cost changed have the following pattern which is expected.
Index Scan ...
Index Cond: ...
Filter: ...
Any thought?
[1] https://postgrespro.com/list/id/8810.1590714...@sss.pgh.pa.us
[2] https://ankane.org/tpc-h
--
Best Regards
Andy Fan
normal.log
Description: Binary data
patched.log
Description: Binary data
reproduce.sql
Description: Binary data
