The latest_revision ViewsFilter has poor performance

This issue is being reviewed by the kind folks in Slack, #needs-review-queue-initiative. We are working to keep the size of Needs Review queue [2700+ issues] to around 400 (1 month or less), following Review a patch or merge request → as a guide.

Reading the comments #29 appears to be addressed in #30.

As a bug believe this will need it's own test case to show the issue is being resolved.

Also posting to #testing to verify that.

I propose also to add new index on node_field_revision table, in my case it extremely increase a performance:
- revision_translation_affected
- nid
- vid

Looks like temporary table constructions in $subquery isn't cached by MariaDB engine. We have match content and more that 100k revisions. Having a dashboard with 4 node revision views (each use "Is Latest Translation Affected Revision" filter) I have the next times of the page render (I make `drush cr` before each load):

Core: 7m 15s
Core+index: 40s
Patch #25: 3m 40s
Patch #25+index: 5s!!!

@smustgrave we can't add tests for query performance, if there's existing test coverage of the latest_revision filter that should be sufficient. Untagging for 'needs tests' but we should verify whether existing tests exist.

@dewalt that seems like a good idea, but should be its own issue, since it'll need an upgrade path etc. Could you open the issue?

Moving back to needs review.

Thanks @catch for cleaning that up.

Opened https://www.drupal.org/project/drupal/issues/3386159 📌 Add new index to node_field_revision Active for #33

#30 is all green but issue summary think could use some love so tagging for that. Proposed solution mainly

@smustgrave did you check whether there is existing test coverage of the latest_revision filter?

Sorry back to needs work.

Drupal\Tests\content_moderation\Unit\LatestRevisionCheckTest
Drupal\Tests\Core\Enhancer\EntityRevisionRouteEnhancerTest
Drupal\Tests\Core\ParamConverter\EntityRevisionParamConverte
Drupal\Tests\migrate\Unit\destination\EntityRevisionTest
Drupal\Tests\node\Unit\Plugin\views\field\RevisionLinkTest
Drupal\Tests\node\Unit\Plugin\views\field\RevisionLinkDelete
Drupal\Tests\node\Unit\Plugin\views\field\RevisionLinkRevert
Drupal\Tests\block_content\Kernel\Views\RevisionRelationship
Drupal\Tests\book\Kernel\BookPendingRevisionTest
Drupal\Tests\block_content\Kernel\Views\RevisionUserTest
Drupal\Tests\content_moderation\Kernel\DefaultRevisionStateTest
Drupal\KernelTests\Core\Entity\ContentEntityNonRevisionableF
Drupal\KernelTests\Core\Entity\EntityNonRevisionableTranslat
Drupal\KernelTests\Core\Entity\EntityRevisionsTest
Drupal\KernelTests\Core\Entity\EntityRevisionTranslationTest
Drupal\KernelTests\Core\Entity\RevisionableContentEntityBase
Drupal\KernelTests\Core\Entity\RevisionRouteProviderTest
Drupal\Tests\jsonapi\Kernel\Revisions\VersionNegotiatorTest
Drupal\Tests\media\Kernel\Views\RevisionUserTest
Drupal\Tests\migrate\Kernel\Plugin\EntityRevisionTest
Drupal\Tests\node\Kernel\Migrate\d6\MigrateNodeRevisionTest
Drupal\Tests\node\Kernel\Views\ArgumentNodeRevisionIdTest
Drupal\Tests\node\Kernel\Views\FilterUidRevisionTest
Drupal\Tests\node\Kernel\Views\ArgumentUidRevisionTest
Drupal\Tests\node\Kernel\Views\RevisionRelationshipsTest
Drupal\Tests\node\Kernel\Views\RevisionUidTest
Drupal\Tests\node\Kernel\Migrate\d7\MigrateNodeRevisionTest
Drupal\Tests\taxonomy\Kernel\PendingRevisionTest
Drupal\Tests\node\Kernel\Views\RevisionCreateTimestampTest
Drupal\Tests\views\Kernel\Entity\LatestRevisionFilterTest
Drupal\Tests\block_content\Functional\BlockContentRevisionDe
Drupal\Tests\block_content\Functional\BlockContentRevisionsT
Drupal\Tests\block_content\Functional\BlockContentRevisionRe
Drupal\Tests\block_content\Functional\BlockContentRevisionVe
Drupal\FunctionalTests\Entity\RevisionRouteProviderTest
Drupal\FunctionalTests\Entity\RevisionViewTest
Drupal\Tests\system\Functional\Entity\EntityRevisionsTest
Drupal\Tests\file\Functional\FileFieldRevisionTest
Drupal\FunctionalTests\Entity\RevisionDeleteFormTest
Drupal\FunctionalTests\Entity\RevisionRevertFormTest
Drupal\FunctionalTests\Entity\RevisionVersionHistoryTest
Drupal\Tests\views\Functional\Update\ViewsFixRevisionIdUpdat
Drupal\Tests\media\Functional\MediaRevisionTest
Drupal\Tests\node\Functional\NodeRevisionsAllTest
Drupal\Tests\node\Functional\NodeRevisionsUiBypassAccessTest
Drupal\Tests\node\Functional\NodeRevisionsTest
Drupal\Tests\node\Functional\NodeRevisionsUiTest
Drupal\Tests\node\Functional\Views\Wizard\NodeRevisionWizard
Drupal\Tests\node\Functional\Views\RevisionLinkTest
Drupal\Tests\views\Functional\Wizard\EntityTestRevisionTes

yes there is coverage

Drupal\Tests\views\Kernel\Entity\LatestRevisionFilterTest

Committed/pushed to 11.x, thanks!

This could theoretically break some hook_query_alter() implementations, so not backporting to 10.1.x. 10.2.x will be out in a couple of months and include this fix.

Thanks @catch

I did a quick grep through the entire contrib space

Only 15 handful of contrib modules using hook_query_alter() and likely very few if any related to the latest_revision Views Filter. I doubt that any of them would be.

bene_event
clockify_report
cloudwords
commerce_cart
communities_view
community_builder
dow_jones_search_clone
entity_query_alter
fullcalendar
ga_reports
google_appliance
image_export_import
local_translation
multiversion
nbox

I'm guessing a very small chance of impact to contrib but possibly custom projects , yet again, very small percentage if any. Way more benefits than risk here.

Automatically closed - issue fixed for 2 weeks with no activity.

After upgrading core from 10.1.5 to 10.2.2 this patch stopped working:
https://www.drupal.org/files/issues/2021-07-27/drupal-views_filter_lates... →

and tested this one, but this also is not applying for me.
Could not apply patch! Skipping. The error was: Cannot apply patch https://www.drupal.org/files/issues/2022-12-16/drupal-views_filter_lates... →

@jakegibs617
you do not need this patch, it's already included in 10.2.2