rfc: update generic query plans

[mgartner]

trafficstars

There was a section in the generic query plans RFC about adding generic query plans to the query cache to be used outside of a prepared statement. I'm not convinced this is all that useful because all statements with placeholders must be prepared, either via an explicit PREPARE or via the extended pgwire protocol. Therefore, all unprepared statements have no placeholders and their fully-optimized plans should already be stored in the query cache and reused.

Epic: None

Release note: None

[cockroach-teamcity]

This change is 

[mgartner]

-- commits line 10 at r1:

Previously, michae2 (Michael Erickson) wrote…

I'm curious what you mean here. I suspect I'm not completely understanding your argument.

For a sequence of events like the following (assuming plan_cache_mode = force_generic_plan):

-- session 1
PREPARE p AS SELECT * FROM foo WHERE a = $1 AND b = $2;
EXECUTE p (10, 20);

-- session 2
PREPARE p AS SELECT * FROM foo WHERE a = $1 AND b = $2;
EXECUTE p (30, 40);

it seems like session 2 should somehow be able to re-use the generic plan we created for session 1. Even if session 2 has to go through preparing the statement, wouldn't it still be beneficial for session 2 to skip creating a generic plan during the EXECUTE p (30, 40)?

Is this what you mean? Or are you thinking about something different?

In the best case with plan_cache_mode=auto you'll reduce total planning cost for a query-session pair from 6 * planning_cost to 5 * planning_cost, since 5 custom plans have to be built before a generic plan can be attempted. There might be a reduction in memory usage, though it's probably minor. I don't think the added complexity is worth it.

[mgartner]

Friendly ping @michae2 :)

[mgartner]

Is there something preventing sessions from also sharing the planCosts via the query cache? If planCosts are also shared, it might be possible for total planning costs for the second session to be reduced to 0/6ths instead of 5/6ths.

No, what you propose is possible. We'd have to carefully iron out some details though, e.g., if we share the plan costs and generic plans across all sessions, then once a generic plan is selected we'll never attempt a custom plan until the cache is cleared. That level of commitment to a single decision will probably lead to poor planning choices at some point, so we'd have to think of a strategy for reassessing custom vs. generic plans.

But to be clear: the reason for updating the RFC is because it doesn't match the behavior of the feature currently, and I don't think there's a strong enough motivation to change the behavior right now.

[mgartner]

TFTR and the ideas!

bors r+

[craig[bot]]

Build succeeded:

• local_roachtest_fips
• macos_arm64_build
• check_generated_code
• unit_tests
• docker_image_amd64
• windows_build
• acceptance
• examples_orms
• linux_amd64_build
• linux_arm64_build
• lint
• linux_amd64_fips_build
• macos_amd64_build
• local_roachtest