Skip to content

Use Commit#notes and Note.for_commit_id when possible to make sure we use all indexes available to us

Douwe Maan requested to merge dm-notes-for-commit-id into master

Quoting myself in https://gitlab.com/gitlab-org/gitlab-ce/issues/34509#note_46295445:

Loading https://gitlab.com/gitlab-org/gitlab-ce/commits/master, a little over 200ms goes into this query, to get note counts for each commit:

SELECT COUNT(*) AS count_all, "notes"."commit_id" AS notes_commit_id 
FROM "notes" 
WHERE "notes"."project_id" = 13083 
AND "notes"."commit_id" IN ('ce7e60f6d77f4c7ad78ec97f3d5b8dcc9b12c03d', '39afe3bc15d27b98938c78a655fa2d7fdfbcaa0a', '859cdd3d3c8b118c665beecd79a059b58de2e1a8', '12dbf5555373863e397a51a51957632054b58073', '61b775edcc5d710055c089aa71898bc01592edaf', '5bfb57903c66235eb606e3b8b632fd42df52921a', '8d51a2f48a8c5319dd2f6bf53de58bfe0a13949d', 'e99ddb6f374c9f79c1c78e808c5e9bd983bed227', 'd7224c81601638dae1dab6fbf72942a19a44074b', '435fd9d073fec75e987a367f0eb9379948f2bbc3', 'e2b28d252654b98b332c060710f82892d25ed138', 'c9659d4c1149fc35e60e64a2cdf3ce5acfa10739', '5427034d8143b2ee715fb8692131eb0062801f76', '1fea51d85c0f47e3a92c36ee4d15dea446ee2cc4', '2b9230af48372dbf7e80d773c772555ec91a5e8b', 'df7eaa85cb5fe9f2d2d42a226fc7f7bf8c19b775', '243530360edac31f309bd3706b2d5117f6d526ad', 'b22f4d2aad4eb1f57502ce9700367d77a92997aa', '5cc3e2354218a34954fb1dfc9d0825183bd8e800', 'df333a9380d3b8836d9278ea4149e82b40ca7bec', '7599009c2726bfdbd73da360961e4d8611641b02', 'c71cf908cd4f289248598d9ea1c144c7b65cbb94', '4da03e9977ea3fa5bb422ab0f23f0a60406c7073', 'c3d15fa620f218c03dea7100a9e72e18458e4ef3', 'cd45895042784e68e61e62c1eaefe52d992ec5de', '9bd3d25512dcd6253de27520145cb203ac287f1b', 'd4a9aedf55793670e40b570b10a1aae6a805d956', '21d8ec1537c0a6d756ea86d001f30f2d62a52247', '0816b2806fc426d529fb6d6af484ee38c296a09a', '8734d1a3e9b1ae7e78e97cc833db78cf883c0ac5', '75c1a27c47cbb126c1ac5c7f28d42f5f73dfc8b1', 'd6b0e23ac4e07598645a577664f74230ae264139', '5af31d6eb0b1bb7b872c5c520cb26d7c7dd5110c', '1f7ecf9a6fee427a7ebe7968418e9cd95774e1de', '309c1e6b712b5839a451299b6b8f6bef7692352c', 'b7273c10ea91c71c8d0eb1d9ecde7983839ffae7', '50e8fe348c638812b8316fb9494aa29b37dcf50f', 'e459deec8529cf1de180eb484a9a70910dff6f74', '63834c9caccff99e5f35c9743baec0f668d438bf', '9de635bd953518c19ceb18bb2156e26ba9e73924') 
GROUP BY "notes"."commit_id"

The query plan looks like this: https://explain.depesz.com/s/yRni

The execution time goes down to about 2ms by simply adding AND noteable_type = 'Commit', so we can actually use the index_notes_on_project_id_and_noteable_type index as it was intended: https://explain.depesz.com/s/q8qD.

Closes https://gitlab.com/gitlab-org/gitlab-ce/issues/34509

Merge request reports

Loading