<p></p>
<p><b>@tomhughes</b> commented on this pull request.</p>

<hr>

<p>In <a href="https://github.com/openstreetmap/openstreetmap-website/pull/5747#discussion_r1983726780">db/migrate/20250304172701_add_notes_user_id_created_at_index.rb</a>:</p>
<pre style='color:#555'>> @@ -0,0 +1,7 @@
+class AddNotesUserIdCreatedAtIndex < ActiveRecord::Migration[7.2]
+  disable_ddl_transaction!
+
+  def change
+    add_index :notes, [:user_id, :created_at], :algorithm => :concurrently
</pre>
<p dir="auto">I'd suggest adding <code class="notranslate">:where => "user_id IS NOT NULL"</code> here so that we don't index anonymous notes.</p>

<hr>

<p>In <a href="https://github.com/openstreetmap/openstreetmap-website/pull/5747#discussion_r1983732029">db/migrate/20250304172700_add_gpx_files_user_id_timestamp_index.rb</a>:</p>
<pre style='color:#555'>> @@ -0,0 +1,7 @@
+class AddGpxFilesUserIdTimestampIndex < ActiveRecord::Migration[7.2]
+  disable_ddl_transaction!
+
+  def change
+    add_index :gpx_files, [:user_id, :timestamp], :algorithm => :concurrently
</pre>
<p dir="auto">I think it would be to index on <code class="notranslate">:user_id, :id</code> here as IDs are a proxy for timestamp. Such an index would already be useful for pagination of a user's traces and can be used for efficient lookup recent traces even if you want to limit by time as this test shows:</p>
<pre class="notranslate"><code class="notranslate">apis_master=# explain select * from gpx_files where user_id = 1 and timestamp > '2024-01-01' order by id desc limit 10;
                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Limit  (cost=2.51..2.51 rows=1 width=106)
   ->  Sort  (cost=2.51..2.51 rows=1 width=106)
         Sort Key: id DESC
         ->  Index Scan using gpx_files_user_id_idx on gpx_files  (cost=0.28..2.50 rows=1 width=106)
               Index Cond: (user_id = 1)
               Filter: ("timestamp" > '2024-01-01 00:00:00'::timestamp without time zone)
(6 rows)

apis_master=# create index on gpx_files(user_id, id);
CREATE INDEX
apis_master=# explain select * from gpx_files where user_id = 1 and timestamp > '2024-01-01' order by id desc limit 10;
                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Limit  (cost=0.28..2.50 rows=1 width=106)
   ->  Index Scan Backward using gpx_files_user_id_id_idx on gpx_files  (cost=0.28..2.50 rows=1 width=106)
         Index Cond: (user_id = 1)
         Filter: ("timestamp" > '2024-01-01 00:00:00'::timestamp without time zone)
(4 rows)
</code></pre>
<p dir="auto">Adding the index switches to a descending index scan on ID which will also be more or less timestamp order and so long as you have some limit on the number of changesets you will consider it should be reasonably efficient?</p>

<p style="font-size:small;-webkit-text-size-adjust:none;color:#666;">—<br />Reply to this email directly, <a href="https://github.com/openstreetmap/openstreetmap-website/pull/5747#pullrequestreview-2665076082">view it on GitHub</a>, or <a href="https://github.com/notifications/unsubscribe-auth/AAK2OLKWVFHQYIJN3SB3TWD2TB433AVCNFSM6AAAAABYIS4BCWVHI2DSMVQWIX3LMV43YUDVNRWFEZLROVSXG5CSMV3GSZLXHMZDMNRVGA3TMMBYGI">unsubscribe</a>.<br />You are receiving this because you are subscribed to this thread.<img src="https://github.com/notifications/beacon/AAK2OLOBOEKUWMU6X6B2UJD2TB433A5CNFSM6AAAAABYIS4BCWWGG33NNVSW45C7OR4XAZNRKB2WY3CSMVYXKZLTORJGK5TJMV32UY3PNVWWK3TUL5UWJTU63HKXE.gif" height="1" width="1" alt="" /><span style="color: transparent; font-size: 0; display: none; visibility: hidden; overflow: hidden; opacity: 0; width: 0; height: 0; max-width: 0; max-height: 0; mso-hide: all">Message ID: <span><openstreetmap/openstreetmap-website/pull/5747/review/2665076082</span><span>@</span><span>github</span><span>.</span><span>com></span></span></p>
<script type="application/ld+json">[
{
"@context": "http://schema.org",
"@type": "EmailMessage",
"potentialAction": {
"@type": "ViewAction",
"target": "https://github.com/openstreetmap/openstreetmap-website/pull/5747#pullrequestreview-2665076082",
"url": "https://github.com/openstreetmap/openstreetmap-website/pull/5747#pullrequestreview-2665076082",
"name": "View Pull Request"
},
"description": "View this Pull Request on GitHub",
"publisher": {
"@type": "Organization",
"name": "GitHub",
"url": "https://github.com"
}
}
]</script>