In cached_plan_cost, we do consider the cost of planning, with the following
algorithm.
int nrelations = list_length(plannedstmt->rtable);
result += 1000.0 * cpu_operator_cost * (nrelations + 1);
I run into a case where 10 relations are joined, 3 of them have
hundreds of partitions. at last nrelations = 421 for this case.
| Plan Type | Estimate Cost | Real Execution Time(ms) | Real Planning
Time(ms) |
| Custom Plan | 100867.52 | 13 | 665.816
|
| Generic Plan | 104941.86 | 33(ms) | 0.76 (used
cached plan) |
At last, it chooses the custom plan all the time. so the final performance
is
678ms+, however if it chooses the generic plan, it is 34ms in total. It
looks
to me that the planning cost is estimated improperly.
Since we do know the planning time exactly for a custom plan when we call
cached_plan_cost, if we have a way to convert the real timing to cost, then
we
probably can fix this issue.
The cost unit is seq_page_scan, looks we know the latency of seq_page
read, we can build such mapping. however, the correct seq_page_cost
detection needs we clear file system cache at least which is
something we can't do in pg kernel[1]. So any suggestion on this topic?
note that both plans have no plan time partition prune and have run time
partition prune, so the issue at [2] probably doesn't impact this.
[1]
https://www.postgresql.org/message-id/flat/20191127164821.lspxyrf3c5r6zu5n%40development#cf34e9db80326709af892ac64bc4cb45
[2]
https://www.postgresql.org/message-id/caku4awqujmqdu9qf_pxxbyetkixhtaxaq_qtx7wxelw27ph...@mail.gmail.com
--
Best Regards
Andy Fan
