I can't execute SQL queries with some joins in a Drupal database

Use EXPLAIN to find out the query plan. This is usually due to joins on non-indexes columns which forces full table scans.

Thank you cilefen!

As you suggested me, I has been thinking that adding indexes in some columns could be the best solution, but could not be the best way because I consider that alter the Drupal database structure is not a clean solution. If somebody tries to install the module where it has this code, also they should know which indexes are necessari to work fine.

I have taken an alternative way. I split the query with small queries, where each query retrieves me a part of the solution.

$query = $this->entityTypeManager->getStorage('node')->getQuery();

$query->accessCheck(FALSE);
$query->condition('status', 1);
$query->condition('type', $node_bundle);
...

//Array where I save the result
$nodes_id = array();

//For each recurrence I make a query
foreach($recurrences as $recurrence){
	
	//Clone base query
	$sub_query = clone $query;
	
	//Get the timestamp start and end
	$date_start = $recurrence['value'];
	$date_end = $recurrence['end_value'];
	
	//Create AND condition
	$andGroup = $sub_query->andConditionGroup();
	$andGroup->condition($smart_date_field_name . '.end_value', $date_start, '>');
	$andGroup->condition($smart_date_field_name . '.value', $date_end, '<');
	
	//Add the condition into the query
	$sub_query->condition($andGroup);
	
	//Show the query
	//dpm($sub_query->__toString());
	
	//Execute the query and save the results into the array
	$nodes_id = array_merge($nodes_id, $sub_query->execute());
	
}

//Load the node objects from ids
$nodes = $this->entityTypeManager->getStorage('node')->loadMultiple($nodes_id);

return $nodes;

Perhaps is not the most elegant way, but it seems that this solution works.