Selecting directly from projection to override ineffecient query plan

Emanuel_Pordes

We have a scenario where the projection being chosen by the query planner yields a sub-optimal total query time.
We can force the usage of a more optimal projection by selecting directly from the projection name instead of the table name.

However, because it is segmented with a K-safety of 1, we need to specify a specific buddy projection to get the query to run. (b0/b1)
I imagine that this query would then break if we were to have some downed nodes.

Is there no way to avoid this by specifying a projection by logical name in a query?
i.e. the name it was given in the CREATE PROJECTION DDL statement?

Colin_Loghin_1

I don't think the query would break when running it with some nodes down. Both projections are required to serve query requests when some nodes are downed. The b0/b1 suffix are actually the logical projection name. The name passed at CREATE projection time, could then be considered a template name.

Have you considered wrapping your projection name in a view ? This would shield the user  in case you need to modify that projection in the future.

Also if the optimizer is not picking the optimal projection and statistics are recent, then I would submit a support case ( assuming you have a support license) for investigation. The use of direct projections in query should be discouraged and is similar to a data base hint ( when the data base does not do the right thing).

Emanuel_Pordes

Are you sure that's correct?

When I want to select directly from a projection created with UNSEGMENTED ALL NODES, I need to add the node name to the query.
e.g. FROM projection_node0001
How is this fundamentally different than specifying which buddy to select from?

Colin_Loghin_1

Indeed, a replicated projection consists of (n) projection objects with different names ( _node000[1..n]) So in that case you are forced to pick an individual name that restricts you to a single node which may go down. In that case the query will fail.

But the use case you stated uses segmented projections. Those do not have node specific names.

Lets say you create projection P1 .. KSAFE 1 for which the system created P1_b0 and P1_b1. When you select from P1_b1 you are not bound to any specific node. If you have a multinode test cluster , try out the scenario with 1 node down.