Postgres driver uses ILIKE statement to make conditions case insensitive. That makes queries a way slower.
I used the following snippet for benchmarking.
$etm = \Drupal::entityTypeManager();
$query = $etm->getStorage('node')
->getQuery()
->accessCheck(FAlSE)
->condition('title', 'test');
echo $query, \PHP_EOL;
echo '--------------', \PHP_EOL;
$start = \microtime(TRUE);
$ids = $query->execute();
$end = \microtime(TRUE);
echo \number_format(1000 * ($end - $start), 3), ' ms', \PHP_EOL;
On my localhost with ~50k nodes dropping LIKE -> ILIKE replacement makes this query 30 times faster.
Active
11.0 🔥
It affects performance. It is often combined with the Needs profiling tag.
Not all content is available!
It's likely this issue predates Contrib.social: some issue and comment data are missing.