cockroachdb
sql: label generic query plans in statement fingerprints > explain plans pages
When generic query plans are enabled with SET plan_cache_mode=auto, the "Explain Plans" page in the console will often show two plans:
One of the plans is the custom plan and the other is the generic plan. Because plan_cache_mode=auto will try at least 5 custom plans per query per session before attempting to use a generic plan, this pattern of having two plans will be common.
To make it easier to understand that one of the plans is generic and one is custom, we should explicitly label the plans by adding a "Generic" column.
Jira issue: CRDB-41218
@dhartunian we think this would help users better understand the difference between plans in the statements page. I'm happy to help out, but would need someone with more experience with the UI to lend a hand. LMK if anyone on your team has the bandwidth in the next few weeks.
We discussed this in a meeting yesterday (11/25) and decided that we want to expose a plan type column below
I misspoke during the meeting and said someone would look at the "plan time" on the page and check the "plan type" to determine generic query plans as the cause. Unfortunately as we can see on the screenshot the page does not have a "plan time" column and only "execution time" column. Although we do surface plan time information in another place on the DB console.
Is it possible to add a "plan time" column on that page alongside a "plan type" for a particular plan gist? Does this substantially increase the scope on your end @dhartunian ? Apologies for the confusion.
cc: @kevin-v-ngo @mgartner
Hi @mgartner and @dikshant, where do we persist "plan type" in crdb_internal.statement_statistics?
On a quick read of the code, I don't think we currently expose the "plan type" in any way (other than showing it in EXPLAIN ANALYZE output), so this issue will require some backend changes too. I can help with that.
It might make sense to include the plan type in the plan gist, in which case, IIUC, it wouldn't have to be stored separately. The motivation to include the plan type in the gist is that if we go ahead with #136938 (which I am leaning towards doing), it would be possible for a generic and custom query plan to have the same gist, which would be awkward in this table and in internal representations.